How to error handle 1004 Error with WorksheetFunction.VLookup?

by

Instead of WorksheetFunction.Vlookup, you can use Application.Vlookup. If you set a Variant equal to this it returns Error 2042 if no match is found. You can then test the variant – cellNum in this case – with IsError:

Sub test()
Dim ws As Worksheet: Set ws = Sheets("2012")
Dim rngLook As Range: Set rngLook = ws.Range("A:M")
Dim currName As String
Dim cellNum As Variant

'within a loop
currName = "Example"
cellNum = Application.VLookup(currName, rngLook, 13, False)
If IsError(cellNum) Then
    MsgBox "no match"
Else
    MsgBox cellNum
End If
End Sub

The Application versions of the VLOOKUP and MATCH functions allow you to test for errors without raising the error. If you use the WorksheetFunction version, you need convoluted error handling that re-routes your code to an error handler, returns to the next statement to evaluate, etc. With the Application functions, you can avoid that mess.

The above could be further simplified using the IIF function. This method is not always appropriate (e.g., if you have to do more/different procedure based on the If/Then) but in the case of this where you are simply trying to determinie what prompt to display in the MsgBox, it should work:

cellNum = Application.VLookup(currName, rngLook, 13, False)
MsgBox IIF(IsError(cellNum),"no match", cellNum)

Consider those methods instead of On Error ... statements. They are both easier to read and maintain — few things are more confusing than trying to follow a bunch of GoTo and Resume statements.

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. Force a screen update in Excel VBA
  3. Set Auto Filtering multiple wildcards
  4. How to make Excel VBA variables available to multiple macros?
  5. Use of symbol # (hash) in VBA Macro
  6. Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
  7. Excel VBA looping through multiple worksheets
  8. When one should use Set [e.g for SpecialCells return value]?
  9. Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
  10. Is there a faster CountIF
  11. Excel VBA How to detect if something was pasted in a Worksheet
  12. Getting error Procedure too large in VBA Macros (Excel)
  13. How to add data validation to a cell using VBA
  14. How to do late binding in VBA?
  15. How to break long string to multiple lines
  16. How to copy a chart from Excel to PowerPoint?
  17. vba paste not working
  18. mac excel vba loop : from list & then export as pdf
  19. Can Advanced Filter criteria be in the VBA rather than a range?
  20. In Excel VBA, what is the way to check if the web page is fully loaded?
  21. Excel Useform: How to hide application but have icon in the taskbar
  22. Reading Web Pages using Excel VBA
  23. Excel VBA: auto click and open file from website
  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