How to error handle 1004 Error with WorksheetFunction VLookup

Question

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

User · Answer

From my limited experience  this happens for two main reasons    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   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

User · Answer

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

User · Answer

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  lt  gt  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

[vba] How to error handle 1004 Error with WorksheetFunction.VLookup?

Examples related to vba

Examples related to excel