0

I'm just a beginner, but I have a simple Vlookup macro that I can't get to work.

Imagine an excel sheet that has a column with different numbers in each row. After a number is selected, the macro shortcut is used and a vlookup should run and return the text associated with that number.

Dim Resource As String
Resource = Selection.Copy
rName = Application.WorksheetFunction.VLookup(Resource, Sheets("Program Title").Range("D5:F305"), 3,   False)
MsgBox "" & rName

End Sub

When I run this code, I get an "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class"

Can someone let me know how this code should be fixed?

Community
  • 1
  • 1
PencilPusher
  • 5
  • 3
  • possible duplicate of ["Unable to get the VLookup property of the WorksheetFunction Class" error](http://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error) – AtAFork Nov 07 '14 at 19:58

1 Answers1

0
Dim Resource As String, rName As Variant
Resource = Selection.Value

rName = Application.VLookup(Resource, Sheets("Program Title").Range("D5:F305"), _
                            3, False)

If Not IsError(rName) Then
    MsgBox "" & rName
Else
    MsgBox Resource & " not found in lookup table"
End If
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thank you! I will research what "As Variant" does. Also, good suggestion on the If statement, it's very useful to handle errors. It threw me off a little because you forgot a "Then" after "If Not IsError(rName)" – PencilPusher Nov 07 '14 at 20:24
  • @PencilPusher, a [Variant](http://msdn.microsoft.com/en-us/library/office/gg251448%28v=office.15%29.aspx) is a variable that can morph into any type of variable, including error objects – SeanC Nov 07 '14 at 22:13