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

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 

2 Answers

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.

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") 

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaHBvZWaBdX%2BOnq%2BcnZxiw6OtjJyYp6xdnLK1ecBmpJqsk516pr7RqKlmrZ6Wr62xjK2mZp%2BVqXq1tMRmpJqsk516sb7OqZyrrKlivKd506GcZq%2Bfpw%3D%3D