VBA Syntax to Lookup Value in a Table

Using:  Excel 2010

I'm trying to code a VBA proc to lookup a filename from the 2nd column in an Excel Table (ideally I would prefer to look this value up based on the Header Label and not based on an index, in case columns are subsequently added), based on a lookup value which corresponds to the value in the 1st column.

Referring to the image below, i'm trying to do something like:

lookupval = "007"
fileNm = Application.WorksheetFunction.VLookup(lookupval, Sheet1.Range("Table1"), 2, False)

where I'm assuming that the third argument in VLookup is an offset from the lookupval column, which would cause the value under column "Filename" to be assigned to 'fileNm'

In my example, given lookupval = "007", I would want to retrieve fileNm = "someotherfilename.pdf" and then open that file in Acrobat Reader.

I'm not clear on the syntax.  I presently get the following MS Visual Basic run-time error '1004':

"Unable to get the VLookup property of the function Worksheet class"

Your help is appreciated.

VBA-Syntax-for-Table-Lookup.png
qengAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Try

Sub GetFilename()
    Dim lookupval As Variant
    Dim fileNm As String
   
    lookupval = 7
    colname = "Filename"
'
    MsgBox ("Index# of filename to get is ") & lookupval
'
    colno = WorksheetFunction.Match(colname, Sheet1.Range("Table1").Resize(1).Offset(-1), 0)
    fileNm = Application.WorksheetFunction.VLookup(lookupval, Sheet1.Range("Table1"), colno, False)
    MsgBox ("Filename to open is '") & fileNm & "'"
    tables
End Sub
0
 
qengAuthor Commented:
OK, I'll add some more info:

I was able to get the following code to run error free in a test workbook, but I will post in the next comment the code in a different workbook which results in an error despite seemingly using the same syntax:

This code retrieves the correct filename with the table shown in the first post above:

Sub GetFilename()
    Dim lookupval As Variant
    Dim fileNm As String
   
    lookupval = 7
'
    MsgBox ("Index# of filename to get is ") & lookupval
'
    fileNm = Application.WorksheetFunction.VLookup(lookupval, Sheet1.Range("Table1"), 2, False)
    MsgBox ("Filename to open is '") & fileNm & "'"
End Sub
0
 
Harry LeeCommented:
Qeng,

You can use this for your example

Option Explicit
Sub LOOKUPTEST()
Dim WS As Worksheet, Tbl As ListObject, ColHeader As String, ColNum As Long, LookupVal As Variant

LookupVal = "007"

Set WS = Sheet1
Set Tbl = WS.ListObjects("Table1")
ColHeader = "Filename"

ColNum = Application.WorksheetFunction.Match(ColHeader, Tbl.HeaderRowRange, 0)
MsgBox (Application.WorksheetFunction.Index(Tbl.ListColumns(ColNum).DataBodyRange, Application.WorksheetFunction.Match(LookupVal, Tbl.ListColumns(1).DataBodyRange)))


End Sub

Open in new window

You can configure the variables LookupVal, WS, Tbl, and ColHeader for your need.
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.