VBA Use Match with Two Criteria to return Row

[ebeck462]

Hi,

I've browsed through multiple threads with varying situations and answers, but cant quite get this to work. So I've decided to post my particular situation if anyone has ideas. I am looking to find the row on a Worksheet based on two criteria taken from Text Boxes on a User Form. "Criteria1" should be searched for in Column A and the "Criteria2" should searched for in Column B of the Worksheet. "matchRow" would then be the row number on the sheet where both are true.

Dim aaoWS As Worksheet
Set aaoWS = ThisWorkbook.Worksheets("AAO")

' Criteria
Dim criteria1 As String, criteria2 As String
criteria1 = TextBox1.Value
criteria2 = TextBox2.Value

' Ranges 
Dim rng1 As Range, rng2 As Range
Set rng1 = aaoWS.Range("A:A")
Set rng2 = aaoWS.Range("B:B")

' Match Row
matchRow = Evaluate("MATCH(1,(criteria1=rng1)*(criteria2=rng2),0)")

MsgBox "Row " & matchRow

I am getting error 2029 for the matchRow. I am assuming I don't have the syntax right for Evaluate but don't know enough about Evaluate to correct it. Thanks if you can help!

 

[James006]

Hi,

With Evaluate, you do need to stick to the formula structure you would have used directly in your sheet ...

In your particular case of an Array Formula, Concatenation is required for Both the lookup_value and for the lookup_array

see a very similar case :

Index and match VBA

I have the table below, but when using the code VBA to search for a value as in below, it appears No match found...

www.mrexcel.com

 

[ebeck462]

James006 thanks for your help. I've tried this two ways and I am coming up with matchRow = Empty when testing using a combination of criteria1 and criteria 2 I know is on the sheet.

matchRow = Evaluate("=MATCH(" & criteria1 & " & " & criteria2 & "," & rng1 & " & " & rng2 & ",0)")

matchRow =Evaluate("=MATCH(1,(" & criteria1 & " & " = " & " & rng1 & ")*(" & criteria2 & " & " = " & " & rng2 & "),0)")

These would be the two ways I know to structure the Match formula. Am i concatenating incorrectly?

 

[James006]

Hi,
Given the fact you are using a UserForm and Strings as your criteria ... the Evaluate formula is a bit tricky ...

Private Sub CommandButton1_Click()
Dim matchrow As Long
Dim aaoWS As Worksheet
Set aaoWS = ThisWorkbook.Worksheets("AAO")

' Criteria
Dim crit1 As String, crit2 As String
crit1 = TextBox1.Value
crit2 = TextBox2.Value

' Ranges
Dim rng1 As Range, rng2 As Range
Set rng1 = aaoWS.Range("A1:A10")
Set rng2 = aaoWS.Range("B1:B10")

' Match Row
matchrow = Evaluate("=MATCH(" & """" & crit1 & """" & "&" & """" & crit2 & """" & "," & rng1.Address & "&" & rng2.Address & ",0)")
MsgBox "Row " & matchrow
End Sub

 

[ebeck462]

Changed "crit" in your code to "criteria" and works perfectly. I will look up more about Evaluate. Seems very handy if you know how to formulate it correctly. Thanks for your help!

 

[James006]

Glad it is all sorted out ...