Translate INDEX/MATCH code from Cell to VBA in excel

[cristopulo]

Hello,

I have an Index/ Match code that basically cross references values in a table to give a numerical output and need help translating it into VBA.

Here is the original code:

=INDEX(T2:AH14;MATCH(B7;S2:S14;0);MATCH(B15;T1:AH1;-1))

Range of Outputs is T2:AH14
Table Header/ Column is T1:AH1 & S2:S14
Cell References are to match with B7 & B15
and -1 specifies the greater value if not exact

This is what Ive got on VBA but im afraid its totally off.

Range("E8"). test=Application.WorksheetFunction.INDEX(Range("T2:AH14"),Application.WorksheetFunction.MATCH("B7",Range("S2:S14"),0),Application.WorksheetFunction.MATCH("B15",Range("T1:AH1"),-1,1)

Thanks in Advance
-Chris

 

[L. Howard]

Try:

Sub Indx_Match()
Range("E8").Formula = "=INDEX(T2:AH14,MATCH(B7,S2:S14,0),MATCH(B15,T1:AH1,-1))"
End Sub

Howard

 

[cristopulo]

Yes,

Worked Perfectly. Wish I had though of inputting it directly as a formula! haha

Thank You
-Chris

 

[L. Howard]

Yes,

Worked Perfectly. Wish I had though of inputting it directly as a formula! haha

Thank You
-Chris

You're welcome.

To add, should your formula have " " or "" in it you will need to double them up as in the example here.

If on the sheet it looks like this:

=IF(F7="W",100,"")

Then in the macro it would look like this:

Sub Watch_The_Quotes()

Range("E9").Formula = "=IF(F7=""W"",100,"""")"

End Sub

Where the quotes that Hold the formula will remain a single " at each end.

Howard