0

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?

Community
  • 1
  • 1
taiko
  • 438
  • 1
  • 8
  • 20
  • `Workbooks("Filename").Sheets("Sheetname").Range("Range")`, filling in for your respective wb,ws, and range should do the trick. – Joe Laviano Nov 19 '13 at 12:51

2 Answers2

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
dosdel
  • 1,118
  • 8
  • 8
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
Sam
  • 7,245
  • 3
  • 25
  • 37
  • thanx a lot for reply, tried your suggestion having used it with the line Sheet1.Cells(X,Y).FormulaR1C1 = "=VLOOKUP(RC[-4], rng, 10, False)". Macro is executing without error, however, when checking cell X,Y I can see "=VLOOKUP(A3, rng, 10, FALSE)" in the content and shell is showing #NAME?. Do you know what the problem can be? – taiko Nov 19 '13 at 14:45
  • I meant shouldn't I see the real range instead of rng variable in the formula? – taiko Nov 19 '13 at 14:51
  • @taiko if you're using your range string like that (rather than the way you first asked `Application.WorksheetFunction.VLookup(` then you'll need to use a string similar to `fullRangeString` (see my edit). this will probably fail if you have spaces in your worksheets name (as they need enclosing with '' for the range to work properly in that case) – Sam Nov 19 '13 at 15:00
  • Ooouh, I see what you mean. Sorry for confusion, I used initially "Application.WorksheetFunction.VLookup", but this gave me an error "Unable to get the VLookup property of the WorksheetFunction class", so I tried diofferent approach, which apparetntly doesn't work either :(. Ihave updated my initial question, so its more clear, what I want to achieve – taiko Nov 19 '13 at 15:28
  • @taiko I've updated. This will look for the value you specify ("test4" in my case) in the sheet you specify and return back the value that it finds in column B next to it. You will get an error if the value doesn't exist. (unable to get the VLOOKUP ...... – Sam Nov 19 '13 at 15:36
  • Thank you very much for help. Your last idea (of course not only the last :) ) did it for me. The error was indeed caused by lookup value not existing in the lookup range. I searched little bit more and rectified it with adding "On Error Resume Next" to the Macro. Now it works fine. thanx a lot again – taiko Nov 19 '13 at 15:54