Excel VBA: Error 1004 WorkSheetFunction 'Unable to get Vlookup property"

Question

Trying to write a quick piece of VBA in Excel 2010 to

• Use Vlookup to find a value
• Return the value in the 3rd Column
• Set a given cell to this value

My difficulty is with the formula.

Sub Metrics123()
    Dim x As Integer        
    x = Application.WorksheetFunction.VLookup("Test", "A7:D9", 3, False)
    Range("A1").Value = x    
End Sub

When I run this I hit the error 1004: 'Unable to get the Vlookup Property of the WorksheetFunction

Any pointers appreciated!

Answer 1

Two ways for you.

1) Use .Formula property:

With ThisWorkbook.Worksheets("Sheet1").Range("A1")
    .Formula = "=VLOOKUP(""Justin"",A7:D9,3,FALSE)"
    .Value = .Value
End With

where .Value = .Value rewrites formula with it's result

2) use Application.VLookup with Range("A7:D9") instead "A7:D9":

Dim x
With ThisWorkbook.Worksheets("Sheet1")
    x = Application.VLookup("Justin", .Range("A7:D9"), 3, False)
    Range("A1").Value = x
End With

Note, that x should be Variant, because if nothing found, Application.VLookup returns Error 2042(#N/A)