Code Application.Match

[JackL]

Hi, please help

I have the following code which works fine:

Debug.Print Application.Match("CAD", wbMaster.Sheets("sheet1").Columns(2), 0)
Debug.Print Application.Match("4k1Z", wbMaster.Sheets("sheet1").Columns(6), 0)

However I need it to return the combined match - as in this worksheet formula:

=MATCH("CAD"&"4k1z",'Master.xls'!ccy&'Master.xls'!fund,0)

But this will not compile in vba:

Debug.Print Application.Match("CAD" & "4k1z", wbMaster.Sheets("sheet1").Columns(2) & wbMaster.Sheets("sheet1").Columns(6), 0)

Please tell me what I am doing wrong.

Thanks in advance!

[Richard Schollar]

You're trying to use an array formula in the Application.Match function (and it won't work like this). You could either generate a look up array based on the two ranges (way too much hassle) or probably use the evaluate function but you will need to restrict the ranges over which the Match operates (you currently have whole column references whereas you need to restrict this to a smaller subset). Eg if you knew the value would be in the first 6000 rows then:

Debug.Print Evaluate("MATCH(""SomeConcatenatedValue"", Sheet1!A2:A6000 & Sheet2!B2:B6000,0)")

[JackL]

thanks for the advice. Is there any other way you know of for setting up VBA equivalent of index and match array formulas?

[Richard Schollar]

You could always write the actual formula to a cell and then use the generated value within the code.