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

Humble Val Published at Dev

Humble Val

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

David Zemens

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-11-18

Comments

0 comments

From Dev

Related Related

Article

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

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

Unable to get Match property of the WorksheetFunction class

Match function error excel vba

Excel VBA - index and match vba error

Excel VBA Error Handling (simple but i can't figure it out)

Can't Get Excel to Print a Specific Worksheet Using VBA

Can't get excel vba function with two arguments to work

Excel, VBA, can't get cell from row and column ID

VBA error handling (INDEX/MATCH in Excel)

Excel VBA - Match Function gives error in loop

Match Not working Excel: Error 1004 Unable to get the Match Property

VBA - (Excel) Why can't I set Textbox type for Userform without getting a type mismatch error?

Excel VBA run-time error 1004 which I can't identify

Can´t add = in formula with VBA, Excel

Can't override Excel shortcuts via VBA

Can't access Excel VBA Code

I can't get my VBA Excel Macro to stop at the end of the row

WorksheetFunction.Match multiple sheets Excel VBA error 1004

Excel VBA: how to solve Index and Match function type mismatch error

Can't get MATCH AGAINST to work with wildcard

Can't get java to match regex with matches()

LWJGL Can't get coordinates to match up

sed can't get string match

Can't get MATCH AGAINST to work with wildcard

Can't get java to match regex with matches()

Can't get jest snapshot match to pass

VBA Error Handling: Unable to get the match property of the worksheet function class

Why do I get Error 2042 in VBA Evaluate INDEX/MATCH?

VBA can't get array to resize

Excel VBA: Match Cell Color