VBA code issue with Match and 2 matching criteria

Question

Trying to use application.match function with 2 match criteria in VBA to return a row number where both criteria exist. I can type directly into my worksheet and get the correct answer, but when converting to VBA code I receive a type mismatch error (13).

This is what I type into the worksheet directly and get the correct row number returned: {=Match(1,("Criteria 1" = A:A)*("Criteria 2" = B:B),0)}. Criteria 1 and Criteria 2 are text strings. Data within column A and column B are a mixture of text and numbers.

This is the equivalent code I tried in VBA which resulted in Type mismatch error:

Result = application.match(1,(Criteria 1 = Range("A:A"))*(Criteria 2 = Range("B:B")), 0)

Tried a shorter test line, and also got type mismatch error:

Test = Application.match(1,(Criteria 1 = Range("A:A"),0)

I expected this code to return the row number (Result) where both Criteria 1 and Criteria 2 existed. The Locals window shows that Criteria 1 is a Variant/String. I tried a cut down version of the code, and got similar error. The result in this case was Test, and the Locals window showed that Test type was Variant/Double, Criteria1 was Variant/String and Range("A:A") is Variant/Variant (I assigned Range(A:A) to a variable to determine this)

Tim Williams Tim Williams · Accepted Answer · 2019-09-09T23:33:31

You can use your original formula via Evaluate

Sub Tester()

    Dim m As Variant, sht As Worksheet

    Set sht = ActiveSheet 'for example

    m = ActiveSheet.Evaluate("=MATCH(1,(""Criteria 1"" = A:A)*(""Criteria 2"" = B:B),0)")

    Debug.Print IIf(IsError(m), "No match!", m)

End Sub

NOTE: use the Worksheet.Evaluate method - if you use Application.Evaluate then your formula will be evaluated in the context of whichever sheet happens to be active, which may not be the sheet you want.

EDIT: adding variables into your formula

Sub Tester2()

    Const FRMLA As String = "=MATCH(1,(""<c1>"" = <r1>)*(""<c2>"" = <r2>),0)"

    Dim m As Variant, sht As Worksheet, f As String
    Dim c1 As String, c2 As String, rng1 As Range, rng2 As Range

    Set sht = ActiveSheet 'for example

    'specify the formula inputs
    Set rng1 = sht.Range("A:A")
    Set rng2 = sht.Range("B:B")
    c1 = "Criteria 1"
    c2 = "Criteria 2"

    'replace the tokens in the formula
    f = Replace(FRMLA, "<c1>", c1)
    f = Replace(f, "<c2>", c2)
    f = Replace(f, "<r1>", rng1.Address(False, False))
    f = Replace(f, "<r2>", rng2.Address(False, False))

    m = sht.Evaluate(f)

    Debug.Print IIf(IsError(m), "No match!", m)

End Sub

VBA code issue with Match and 2 matching criteria

1 Answers