Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to error handle 1004 Error with WorksheetFunction.VLookup?

Tags:

excel

vba

I have this code:

Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim ws As Worksheet: Set ws = Sheets("2012")
Dim rngLook As Range: Set rngLook = ws.Range("A:M")

'within a loop
  currName = "Example"
  cellNum = wsFunc.VLookup(currName, rngLook, 13, False)

VLookup is not expected to always find a result; but when it does not find a result the line errors out before I can even error check it the next line.

The error:

Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class

It works fine when a result is found. What's a good way to handle errors here?

like image 927
JBurace Avatar asked Aug 05 '13 16:08

JBurace


People also ask

How do I fix Runtime Error 1004 Application defined or object defined error?

In order to do so, go to VBE (Alt + F11) and right click on the module's icon. Insert a new module and enter your code there. These situations are the most common for error 1004 to occur. I hope it fixed your problem.

What does Vlookup return if not found VBA?

If the VLOOKUP function does not find an exact match, it will return the #N/A error. By using the IF and ISNA functions, you can return a "Yes" value if an exact match is found. Otherwise, a "No" value is returned.


2 Answers

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.

like image 174
Doug Glancy Avatar answered Nov 15 '22 18:11

Doug Glancy


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.WorksheetFunction
Dim 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 anyway
cellNum = wsFunc.VLookup(currName, rngLook, 13, 0)

If Err.Number <> 0 Then
''error appeared
    MsgBox "currName not found" ''optional, no need to do anything
End If

On Error GoTo 0 ''no error, coming back to default conditions

End Sub
like image 45
Purchawka Avatar answered Nov 15 '22 18:11

Purchawka