How to error handle 1004 Error with WorksheetFunction.VLookup?

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 StringDim cellNum As Variant'within a loopcurrName = "Example"cellNum = Application.VLookup(currName, rngLook, 13, False)If IsError(cellNum) Then    MsgBox "no match"Else    MsgBox cellNumEnd IfEnd 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.

There is a way to skip the errors inside the code and go on with the loop anyway, hope it helps:

Sub new1()Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunctionDim ws As Worksheet: Set ws = Sheets(1)Dim rngLook As Range: Set rngLook = ws.Range("A:M")currName = "Example"On Error Resume Next ''if error, the code will go on anywaycellNum = wsFunc.VLookup(currName, rngLook, 13, 0)If Err.Number <> 0 Then''error appeared    MsgBox "currName not found" ''optional, no need to do anythingEnd IfOn Error GoTo 0 ''no error, coming back to default conditionsEnd Sub

From my limited experience, this happens for two main reasons:

1. The lookup_value (arg1) is not present in the table_array (arg2)

The simple solution here is to use an error handler ending with Resume Next

2. The formats of arg1 and arg2 are not interpreted correctly

If your lookup_value is a variable you can enclose it with TRIM()

cellNum = wsFunc.VLookup(TRIM(currName), rngLook, 13, False)