Copied Excel Macro to VBA code.

[Mellstock]

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?

[Paul_Hossler]

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

[Mellstock]

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?

[Mellstock]

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.