Trying to write a quick piece of VBA in Excel 2010 to
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!
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)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments