Failed to get vlookup to work

There is nothing wrong with the code. You see the result of what happens when you use the function version WorksheetFunction

and no result is returned. In particular, they throw an error and interrupt VBA execution. In this case, if you tried to use the same formula in a workbook and not in VBA, you will get some form of error ( #N/A

or #VALUE!

possibly).

If you want to prevent this, the easiest way is to switch to using Application.VLookup

instead Application.WorksheetFunction.VLookup

. Although Intellisense does not support this feature, it behaves the same as the other, with the exception of error handling. If the version of the function does not WorksheetFunction

have an error, it will return an error rather than throw it. This allows you to check for an error and then continue with your code.

If you think you need to find the value with here VLOOKUP

, then you can start checking for inconsistencies between text / numbers and other things. I would check with formulas, not in VBA.

Here's an example using a different functional form and capturing the error.

Sub LinkPolicyNum()
    Dim r As Integer
    Dim policynum As Variant
    Dim lookup_num As Range
    Dim policybox As Variant

    r = ActiveCell.Row
    'Row number of the Selected Cell

    policynum = ActiveSheet.Cells(r, 3).Value

    Set lookup_num = ThisWorkbook.Sheets("PolicyDetails").Range("a1:z5000")

    policybox = Application.VLookup(policynum, lookup_num, 3, False)
    'to match the policy number to the policy details

    If IsError(policybox) Then
        'possibly do something with the "not found" case
    Else
        MsgBox policynum
        MsgBox policybox
    End If

End Sub

      

        
        
        
      

    

Link to this issue: http://dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/