Search:

Type: Posts; User: DKenny

Page 1 of 13 1 2 3 4

Search: Search took 1.70 seconds.

  1. Replies
    2
    Views
    12,253

    Re: Goal Seek Iteration Code in VBA? Possible?

    Or you could just call the GoalSeek method of the target cell.

    Excel Developer Reference
    Range.GoalSeek Method
    Calculates the values necessary to achieve a specific goal. If the goal is an...
  2. Re: Excel: How to: Pass Command Line Parameter [DKenny is KING!]

    Wow, this is a blast from the past!

    I would suggest reversing the string, using the StrReverse function.
    Then use the Find Fuction to locate the 'e' switch.
    Take a sub-string of the string to...
  3. Replies
    14
    Views
    7,322

    Re: Exporting Graph for MS Access to Powerpoint

    Here's some code to copy a picture from the clipboard.
    The Excel file shows this in action, by copying the chart as a picture and pasting it to the form.

    Option Explicit

    'Requires a reference...
  4. Replies
    2
    Views
    659

    Re: shared event handling

    Have a look at my post here on creating an event handler class in VBA
    http://www.vbforums.com/showthread.php?p=2519617#post2519617
  5. Replies
    5
    Views
    958

    Re: Control Array in VBA

    Have a look at my post here on creating an event handler in VBA. It will give you a good approxamation of a control array.
  6. Replies
    3
    Views
    556

    Re: copy field in access macro or rules?

    I assume you are talking about access?

    Use the following SQL statement

    UPDATE TABLE TableName SET b = right(a,16)
  7. Replies
    3
    Views
    588

    Re: Open New & Close Old (.xls)

    Replace these 2 lines
    Workbooks("a.xls").Select
    ActiveWorkbook.close

    With this one line
    Workbooks("a.xls").Close
  8. Re: Is there something Special about Excel 2003?

    Have you been able to determine which line in your code is causing the error?
  9. Re: How do I populate a drop-down listbox (combobox?) from column headings?

    Just convert both values to upper case before you compare them

    If UCase(ActiveSheet.Cells(1, columnnum).Value) = UCase(Inputvalue) Then
  10. Re: Application won't run with dif. versions of Excel?

    Fleet
    The answer is to not specify the version of Excel at all!

    Have a search on this forum for posts about "Late Binding". There are multiple examples of how to make your application agnostic to...
  11. Re: Loop through a table and select multiple records

    I hate to say ity, but you may want to rethink your approach. This sounds like a situation that would be better handled by a database than by a spreadsheet.
  12. Re: Loop through a table and select multiple records

    OK, would the pivot table approach work? See attached...
  13. Re: Loop through a table and select multiple records

    If this is Excel, you don't need a procedure to achieve this. You have two options that both use native Excel functions.
    1/ Create a Pivot Table
    or
    2/ Use the SUMIF function

    If the list of...
  14. Re: Transferring records from one Table to another

    As the datatype is numeric, you will need to remove the single quotes from the condition in the WHERE clause

    SQL = "insert into [Tracking Archive] select * from [Tracking] where DischargeID = " &...
  15. Re: Transferring records from one Table to another

    What is the datatype for the DischargeID filed?
  16. Re: Transferring records from one Table to another

    SQL looks fine - assuming that
    1/ the Tracking table contains a field called DischargeID
    2/ the DisID is being correctly populated

    On your additional comments to your previous post, it doesn't...
  17. Re: Transferring records from one Table to another

    You are on the right track white using a WHERE in the INSERT INTO statement. Can you post the INSERT SQL for the transaction table?
  18. Re: Transferring records from one Table to another

    Forget about the form and think about the underlying tables for a moment (there is no spoon).
    There must be some FK relationship between the master table and the transaction table. If you can...
  19. Re: How to use Autofilter and delete the results?

    There is no need to autofilter to delete the rows.
    Just loop through all rows, looking for the value 3 and delete based on that.

    Sub BulletHeadDelete()
    Dim rngColumn As Range
    Dim lMaxRow As...
  20. Replies
    7
    Views
    744

    Re: Creating a Date formula in Excel?

    I think you've over-engineered it a tad.
    Putting my formula "=NOW()-WEEKDAY(NOW(),2)+1" in A2 give the correct answer.

    B2 then becomes =A2+1
    C2 =B2+1
    Etc
  21. Thread: SUM Question

    by DKenny
    Replies
    3
    Views
    416

    Re: SUM Question

    That's why I said to "Insert a new column" ;)

    Glad you were able to get it resolved.
  22. Re: Transferring records from one Table to another

    you need to run two SQL statements in sequence. One to insert the record into the history table, the second to delete from the active table.

    Here's the sudo-SQL
    SQL 1/
    INSERT INTO...
  23. Replies
    7
    Views
    744

    Re: Creating a Date formula in Excel?

    For Monday use the following formula

    =NOW()-WEEKDAY(NOW(),2)+1

    For each day after Monday, increment the adder value at the end.
    i.e Tuesday = "=NOW()-WEEKDAY(NOW(),2)+2"
  24. Thread: Not in List

    by DKenny
    Replies
    3
    Views
    475

    Re: Not in List

    Why not just use a vlookup formula on the worksheet to try to find matches?

    Any value that doesn't have a match will return an #N/A error
  25. Thread: SUM Question

    by DKenny
    Replies
    3
    Views
    416

    Re: SUM Question

    Insert a new column and add the following formula in that column. This will trap the errors and treat them as zeros. You can then sum the results in that col.

    =IF(ISERROR(A1),0,A1)
  26. Replies
    4
    Views
    513

    Re: [RESOLVED] Color change for Cell Values

    Try this - Basically it loops through the cells in a column, building an array of the used values and setting the colr based on the order of those used values.

    Sub Dastard_Color()
    Dim...
  27. Thread: Excel 2003

    by DKenny
    Replies
    2
    Views
    461

    Re: Excel 2003

    You will need to have your headers in Row 1.
  28. Thread: update links

    by DKenny
    Replies
    11
    Views
    1,113

    Re: update links

    Try this
    Sub ChangeAllLinks()
    Dim wkbLinked As Workbook
    Dim fdNewPath As FileDialog
    Dim sNewPath As String
    Dim vLinks As Variant
    Dim lLinkNum As Long
    Dim sReversed As String
    Dim sFileName As...
  29. Thread: update links

    by DKenny
    Replies
    11
    Views
    1,113

    Re: update links

    Here's the code to change all links in a workbook to a new path.

    Option Explicit

    Sub ChangeAllLinks()
    Dim wkbLinked As Workbook
    Dim fdNewPath As FileDialog
    Dim sNewPath As String
    Dim vLinks...
  30. Replies
    7
    Views
    4,990

    Re: Excel Union of Ranges

    This is quick and dirty ( 'cause I 'm leaving for the Holidays) but it may help....

    Sub testZaZa()
    Dim rngZaZa As Range
    Dim lAreaNum As Long
    Dim lColNum As Long

    Set rngZaZa =...
  31. Replies
    7
    Views
    4,990

    Re: Excel Union of Ranges

    You need to look at the "Areas" collection of the Range object. This collection contains a group of ranges that constitutes the non connected ranges that make up the master range.

    That is probably...
  32. Replies
    12
    Views
    873

    Re: Assigning a value to a cell in Excel

    Joseph is right, you could use an IF statement in E4 that is dependant on D4

    something like (in E4)
    =IF(D4="Somevalue","new value","")
  33. Replies
    12
    Views
    873

    Re: Assigning a value to a cell in Excel

    what value do you need to assign to what cell?
  34. Replies
    10
    Views
    2,802

    Re: Increase Excel 2000 Max rows Limit

    Not the version I've seen....

    Excel 2007 Big Grid
  35. Re: Excel Macro Help - Sum from another sheet

    Why is it not that easy?
  36. Re: Excel Macro Help - Sum from another sheet

    There is no need to use VBA to achieve what you need. You can achieve all that you require using the SUM and COUNT excel worksheet functions.


    Put the following formula into Cell B3 on Sheet1...
  37. Thread: Date function

    by DKenny
    Replies
    4
    Views
    551

    Re: Date function

    Humm, that code works fine for me...



    If the NOW function works, can I suggest that you use it and format the textbox as a short date - that way you wont see the time element.
  38. Thread: Date function

    by DKenny
    Replies
    4
    Views
    551

    Re: Date function

    You need to specify the .value property of the textbox object.

    Private Sub Command2_Click()
    Me.Text0.Value = Date
    End Sub
  39. Re: Selecting And Comparing Two Entire Columns FOR TWO DIFFERENT VALUES

    =SUM((F1:F65535="Pipe")*(C1:C65535="1CC")*(D1: D65535))
  40. Re: Changing the font size for a whole sheet

    FontSize is a valid property, but only of the TextEffectFormat Object. Intelisence puts it in CamelCase because it recognizes it as a valid vba expression.
Results 1 to 40 of 500
Page 1 of 13 1 2 3 4



Click Here to Expand Forum to Full Width