Using Match with 2 criteria in VBA

[Do0ge]

I'm having trouble with using index/match with 2 criteria in VBA.

On the workbook it's simply:
{=MATCH(criteria1&criteria2,A2:A1001&B2:B1001,0)

In VBA I have (to be done with a sheet called 'Market Data')
tomatch = Application.WorksheetFunction.Match(criteria1 & criteria2, Sheets("Market Data").Range("A2:A1001") & Sheets("Market Data").Range("B2:B1001"), 0)
price = Evaluate(tomatch)

But this gives me a #VALUE error. I've also tried putting the entire first line in quotes, as I've seen in some other posts, but then I get a "compile error: expected end of statement". Everything is dimmed as a variant.

Any help would be appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if you can make use of this (of course with your own actual values for criteria1 and criteria2)
It will return 0 if no match is found.

Sub Check_Match()
  Dim criteria1 As String, criteria2 As String
  Dim tomatch As Long
  Dim wsAct As Worksheet
   
  criteria1 = "b"
  criteria2 = "a"
  Set wsAct = ActiveSheet
  Application.ScreenUpdating = False
  Sheets("Market Data").Activate
  On Error Resume Next
  tomatch = Evaluate("match(""" & criteria1 & criteria2 & """,A2:A1001&B2:B1001,0)")
  On Error GoTo 0
  wsAct.Activate
  Application.ScreenUpdating = True
  MsgBox tomatch
End Sub

 

[Do0ge]

Thank you! That helped. Though another problem is that one of my criteria inputs was a data, and VBA wasn't able to handle that correctly, but I was able to get it to work by changing the date from a date format to string format.

 

[Peter_SSs]

Glad you got it working.