Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"

Humble Val picture Humble Val · Jul 19, 2013 · Viewed 89.5k times · Source

For the love of all that is good, I cannot seem to get this to work. I keep getting the error mentioned above.

I have this table, and I'm trying to find out whether the code matches it's own sub-code somewhere within the other column, however it's erroring out. Your help is greatly appreciated.

enter image description here

Sub testing()

    Dim m1 As long
    Dim myrange As Range

    Set myrange = Worksheets("Sheet1").Range("B2:B23")

    For e = 2 To 23
        m1= Application.WorksheetFunction.Match(Cells(e, 1).Value, myrange, 0)

        If m1 > 0 Then
            Cells(e, 3).Value = "Yes"
        Else
            Cells(e, 3).Value = "No"
        End If
    Next e

MsgBox "Complete!"

End Sub

Answer

David Zemens picture David Zemens · Jul 19, 2013

Use the Application.Match function which allows for better ability to trap errors. When using the WorksheetFunction.Match, when a match is not found, it returns an error, which is what you're experiencing.

If Not IsError(Application.Match(Cells(e, 1).Value, myrange, 0)) Then
    'Do stuff when the match is found
    Cells(e, 3).Value = "Yes"
Else:
    Cells(e, 3).Value = "No"
End If

You could also potentially use the CountIf function:

If Application.WorksheetFunction.CountIf(myRange, Cells(e,1).Value) > 0 Then
    Cells(e,3).Value = "Yes"
Else:
    Cells(e,3).Value = "No"
End If

Neither of these approaches requires you to use the m1 variable, you can assign this variable within the True part of the If/Then statement, if you need to identify where the match is found.