vba - Unable to get the vlookup to work

Question

I run this set of codes and it returns an error:

Run-time error '1004' Unable to get the Vlookup property of the WorksheetFunction class.

I already have one vlookup in another sub routine. Is there anything wrong with this code ? I debug and the error appears at the policybox there.

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.WorksheetFunction.VLookup(policynum, lookup_num, 3, False)
    'to match the policy number to the policy details

    MsgBox policynum
    MsgBox policybox



End Sub

Answer 1

There appears to be nothing wrong with your code. You are seeing the result of what happens when you use the WorksheetFunction version of functions and no result is returned. Specifically, they throw an error and interrupt execution of the VBA. In this case, if you tried the same formula in the workbook instead of in VBA, you would get some form of error (#N/A or #VALUE! possibly).

If you want to prevent this from happening, the easiest thing to do is to change to using Application.VLookup instead of Application.WorksheetFunction.VLookup. Although there is no Intellisense to help with this function it behaves the same as the other except for error handling. If the non-WorksheetFunction version of a function has an error, it will return the error instead of throwing it. This allows you to check for an error and then carry on with your code.

If you think you should be finding a value with VLOOKUP here then you can start checking for mismatches between text/numbers and other things like that. I would check with formulas and not in VBA though.

Here is an example of using the other functional form and trapping 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

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