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
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.