Excel VBA Index inside of match function

user_12096264 0
Viewed 120

I've got this chunk of code that works when I enter it inside of cell

=MATCH(1;INDEX(($B:$B="Some_value1")*($D:$D="Some_value2");0);0)

When I "translate" it to VBA code I get typemismatch error.

Debug.Print Application.WorksheetFunction.Match(1, Application.WorksheetFunction.Index((Range("B:B") = "Some_value1") * (Range("D:D") = "Some_value2"), 0), 0)

So can anyone point me where my mistake is or am I even doing it right?

Thanks in advance

match indexing vba excel
1 Answers

Keeping in mind that the inner matrix reflects a condition based on worksheet logic (returning an array of 0 or 1 cell values) it seems that you have to execute an evaluation at least within the INDEX part:

Debug.Print WorksheetFunction.Match(1, WorksheetFunction.Index(Evaluate("($B:$B=""Some_value1"")*($D:$D=""Some_value2"")"), 0), 0)

or to evaluate the entire expression:

Debug.Print Evaluate("=MATCH(1,INDEX(($B:$B=""Some_value1"")*($D:$D=""Some_value2""),0),0)")
user_64602970
Related