Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”

by

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.

More Related Contents:

  1. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  2. Vba Excel do vlookup from a closed file
  3. to make this code simpler
  4. VBA code takes long time to execute in excel
  5. Excel VBA – A general how to
  6. VBA code required for Msg Box when two value equal in two cells
  7. VLOOKUP with multiple criteria returning values in one cell
  8. Excel VBA deleting rows in a for loop misses rows
  9. How To Speed Up VBA Code
  10. What is the lifetime of a global variable in excel vba?
  11. Download attachment from Outlook and Open in Excel
  12. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  13. Embed picture in outlook mail body excel vba
  14. How to check for empty array in vba macro [duplicate]
  15. Use autofilter on more than 2 criteria
  16. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  17. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  18. Load csv file into a VBA array rather than Excel Sheet
  19. Insert line break in wrapped cell via code
  20. VBA – Range to jpg picture
  21. Change color of certain characters in a cell
  22. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  23. Inserting an Online Picture to Excel with VBA
  24. Change length of For loop while in the loop
  25. VBA Macro crashes after 32000 rows
  26. How to create dynamic variable names VBA
  27. How to break a long string into multiple lines
  28. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  29. simple vba code gives me run time error 91 object variable or with block not set
  30. VBA inheritance, analog of super

Leave a Comment