Consulting

Results 1 to 4 of 4

Thread: Copied Excel Macro to VBA code.

  1. #1

    Copied Excel Macro to VBA code.

    I have a function which works in Excel and am trying to build a program using this function. The function is

    =Index(C2:C34,Match(I7&I8,A2:A34&B2:B34,0)) It works a treat in Excel 365 I need Ctr Shift Enter added {} around the function if I use a computer with Excel 2010. (This one I am posting on)

    If I record a Macro to look at look the code I get:

    Sub FindOpen()
    ' FindOpen Macro
        Selection.FormulaArray = _
            "=INDEX(R[-7]C[-6]:R[25]C[-6],MATCH(R[-2]C&R[-1]C,R[-7]C[-8]:R[25]C[-8]&R[-7]C[-7]:R[25]C[-7],0))"
    End Sub
    I have had two goes at translating the code detailed below, neither of which work. I get a compile error expected end with the "C2" highlighted.

    Sub FindOpenA()
    
    ' FindOpen Macro
    Range("I9").Select
    Selection.FormulaArray =
            "=Worksheet.Function.INDEX(Range("C2"):Range("C34"),Worksheet.Function.MATCH(Range("I7")&Range("I8"),Range("A2"):Range("A34")&Range("B2"):Range("B34"),0))"
    
    
    Selection.FormulaArray = _
    "=Application.WorksheetFunction.Index(Range("C2:C34"), Application.WorksheetFunction.Match(Range("I7") &Range("I8"), Range("A2:A34") & Range("B2:B34"), 0))" 
    
    End Sub
    How should I be going about this?
    Last edited by xld; 03-26-2020 at 10:52 AM. Reason: Added code tags

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,000
    Location
    Without actually playing with it, you'e building a String that looks like a formula. To have a quote in the string, you need to double the quotes in the VBA, but this issue will go away

    Also, since you're making the equivalent of a cell formula, you don't need the Application.WorksheetFunction

    You don't use Range("I7") directly in the code, you use Range("I7").Address to build up the string

    "=INDEX(" & Range(C2).Address & ":" & Range("C34").Address & ",MATCH(" & Range(I7).Address & "&" & Range(I8).Address &", " & _
    Range(A2).Address & ":" & Range(A34).Address & "&" & Range(B2).Address & ":" & Range(B34).Address & ",0))"
    
    I've found the easiest way to debug things like this is to use a variable and then the Immediate Window to verify that I'm constructing the correct formula
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Hi Paul thank you for the suggestion about splitting up the expression and using variables along the way. The Index function works fine.

    Selection.Value = WorksheetFunction.Index(Range("F2:F34"), 19) 'returns the 19th value in the Range. so far so good.

    The Match function works well like this with only one column (Date) being searched.
    b = WorksheetFunction.Match(Range("J3"), Range("A2:A34"), 0) 'returns a date from a column of dates

    I still however get a compile error when I try to search on two columns Date and Time
    b = WorksheetFunction.Match(Range("J3") & Range("J4"), Range("A2:A34") & Range("B2:B34"), 0)

    It is a bit puzzling as it works ok when written as a formula in Excel and when it is recorded as a Macro.
    I want to be able to code it into vba so eventually I can enter the currently hard coded J3 (Date) and J4 (Time) as variables.

    Any further hints or suggestions from you or anyone else?



  4. #4
    Should I be opening a new thread on this? Something like Match Worksheet function in vba, searching on multiple criteria?

    Or having replied as I have leave it be. Do not wish to upset anyone as I am relatively new.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •