Consulting

Results 1 to 3 of 3

Thread: Solved: Using VLOOKUP with array in different workbook and referencing the filename

  1. #1
    VBAX Newbie
    Joined
    Apr 2007
    Posts
    5
    Location

    Solved: Using VLOOKUP with array in different workbook and referencing the filename

    Hey all. I've got 2 different workbooks I want to compare the data between, sort of an audit thing. In order to get specific data for comparison I was thinking of using a vlookup function. That's no problem. My issues arrive because the names of the workbooks I need to look up the data in change based on what days I'm looking up.

    Now I've got a macro already opened which puts the path and filename as a string in a range name cell. Is there a way to incorporate the rangename into the vlookup function? Sort of like:

    =vlookup(Lookup_value,'[RangeName]Sheet!Array]

    If I simply type in the function with that layout it just throws an open dialog box at me to specify the file, but I want it to use the path specified in the Rangename cell.

    If there's a better way to do it I'm open to suggestions.

    Thanks,

    Binoy

  2. #2
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi Binoy,

    Typically, what I do is create a named range via code and then just use that in my lookup or index/match formulas. It's easy enough to do, and I find that it makes placing the formulas in the worksheets SOOO much easier, particularly if you need to set anything using RC referencing. (Maybe just me, but there you have it.)

    This is untested, but I think it should work:
    [vba]Dim wbTarget As Workbook
    Set wbTarget = Workbooks("TheWorkbookWithTheTable.xls")

    'Set the name
    ThisWorkbook.Names.Add "tblData", wbTarget.Worksheets(1).Range("A1:G400")

    'Put your vlookup here
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-1],tblData,false)"[/vba]

    Sometimes I have need to do the above, then paste the values and get rid of the range names as well. To delete the names:
    [vba]'Delete the name
    On Error Resume Next
    For Each nm In ThisWorkbook.Names
    If nm.Name = "tblData" Then nm.Delete
    Next nm
    On Error GoTo 0[/vba]

    HTH,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  3. #3
    VBAX Newbie
    Joined
    Apr 2007
    Posts
    5
    Location
    Thanks for the help, I'll try that out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •