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

Question

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.

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 1

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.

Answer 2

Just as another option, this can also be done by putting the formula below in cell C2, and dragging it down to C23.

=IF(COUNTIF($A$2:$A$23,B2)>=1,"YES","NO")