Mellstock
03-26-2020, 08:00 AM
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?
=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?