VBA reference to a table in different worksheet

Question

I'm trying to use vlookup in VBA.

1. Filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
2. Table1 = Sheet1.Range("A3:A7000") ' SiteID
3. Table2 = [Filename]Sheet1.Range("A3:I13")
4. Roww = Sheet1.Range("E2").Row
5. Coll = Sheet1.Range("E2").Column
6. For Each cl In Table1
7. Sheet1.Cells(Roww, Coll) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
8. Roww = Roww +1
9. Next cl

My question is how do I define the table located in different worksheet/file on line 3?

Answer 1

You can set the workbook, worksheet and range to objects in VBA and refer to them by name..

example:

Sub test()

Dim filename As String
Dim fullRangeString As String

Dim returnValue As Variant
Dim wb As Workbook
Dim ws As Worksheet

Dim rng As Range

    'get workbook path
    filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")


    'set our workbook and open it
    Set wb = Application.Workbooks.Open(filename)

    'set our worksheet
    Set ws = wb.Worksheets("Sheet1")

    'set the range for vlookup
    Set rng = ws.Range("A3:I13")


    'Do what you need to here with the range (will get error (unable to get vlookup property of worksheet) if value doesn't exist
    returnValue = Application.WorksheetFunction.VLookup("test4", rng, 2, False)


    MsgBox returnValue
    'If you need a fully declared range string for use in a vlookup formula, then
    'you'll need something like this (this won't work if there is any spaces or special
    'charactors in the sheet name



    'fullRangeString = "[" & rng.Parent.Parent.Name & "]" _
                        & rng.Parent.Name & "!" & rng.Address

    'Sheet1.Cells(10, 10).Formula = "=VLOOKUP(A1," & fullRangeString & ",8,False)"




    'close workbook if you need to
    wb.Close False


End Sub

Answer 2

This should work:

Sub vLook()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Range1, myValue
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("C:\Code\Book4.xlsx")

Range1 = wb2.Sheets(1).Range("A1:C5")
myValue = Application.WorksheetFunction.VLookup("Test", Range1, 2, False)

End Sub