Excel VBA: Using 2 variables with Match

Question

ws2.Range("D3").Value = Application.WorksheetFunction.Match(variable_i, ws2.Range("A:A"), 0)

ws1 = worksheet 1 ws2 = worksheet 2

• On ws1 I have 2 columns of drop down boxes (data validation list)

• the selection from the first column of drop down boxes is assigned to the variable "variable_i"

• the selection from the second column of drop down boxes is assigned to the variable "variable_p"

• It pulls its data from ws2 (the range is all of column a)

• It is looking for the selection from the drop down box with the variable "variable_i"... the code works fine, it returns the number row on D3 without any issues

• I'm looking for it to return the row number that contains both variables in the 2 columns (variable_i and variable_p)

• I've tried the following code:

ws2.Range("D3").Value = Application.WorksheetFunction.Match(variable_i & variable_p, ws2.Range("A:B"), 0)

as I had seen that online, but it doesn't work for me.

The error I get is: "Run-time error '1004': Method 'Match' of object 'WorksheetFunction' failed

What would be the best way to have multiple variables be the lookup?

Any help would be much appreciated!

Answer 1

The key for you I believe could be to use EVALUATE() function. Multiple criteria would mean you most likely need an array which EVALUATE will recognize. Also concatenating values and ranges to check for two criteria is definately not the best solution. Imagine cell A1=1 and B1=101. What happens if you concatenate ranges to 1101 and you look for criteria1 10 and 11 instead of 1 and 101?

A simple evaluate example:

Debug.Print Evaluate("MATCH(1,(A1:A6=1)*(B1:B6=""B""),0)")

In my example I have one Long and one String variable, respectively 1 and B. Would I use your named variable variable_i and variable_p I could make a code like:

ws2.Range("D3") = Evaluate("MATCH(1,('Sheet1'!A1:A6=" & variable_i & ")*('Sheet1'!B1:B6=""" & variable_p & """),0)")

Notice the difference of adding a Long or a String variable? That's because to evaluate correctly in VBA there needs to be two quotation marks surrounding a String variable. A number can do without.

Also notice I specified sheet names in the code, you can change that according to your needs