Application.Match gives type mismatch

by

In all likelihood, no match is found. In such a case, Application.Match returns an Excel error code i.e. a Variant/Error whose value is Error 2042 (this corresponds to getting #N/A in Excel).

Such an Error value cannot be implicitly coerced to a String (which is what MsgBox expects) and thus you get the type mismatch.

Note that the same Match function can be called using WorksheetFunction.Match. The only difference is how errors are to be handled:

  • With WorksheetFunction, errors are treated as VBA errors, trappable using the On Error syntax.

  • With Application, they return an Excel error code wrapped in a Variant. You can use IsError to see if the returned variable is an Error type variant.

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. Wait until ActiveWorkbook.RefreshAll finishes – VBA
  4. Pop up the Excel Statusbar?
  5. Excel VBA deleting rows in a for loop misses rows
  6. Execute a command in command prompt using excel VBA
  7. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  8. Copy data from another Workbook through VBA
  9. how to detect whether VBA excel found something?
  10. Excel VBA Load worksheet in Function
  11. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  12. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  13. Determine if cell contains data validation
  14. Load csv file into a VBA array rather than Excel Sheet
  15. Insert line break in wrapped cell via code
  16. Change color of certain characters in a cell
  17. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  18. VBA Macro crashes after 32000 rows
  19. TextJoin UDF For Excel 2013
  20. How to create dynamic variable names VBA
  21. How to break a long string into multiple lines
  22. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  23. simple vba code gives me run time error 91 object variable or with block not set
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment