+ Reply to Thread
Results 1 to 4 of 4

vlookup with variable for filename and path wants to append the filename after sheet name

  1. #1
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    vlookup with variable for filename and path wants to append the filename after sheet name

    Can anyone tell me why Excel wants to try an append the filename again at the end of this vlookup?

    Dim Path As String
    Dim FileNm As String
    Dim Wb As String

    Sheets("Product Receipt").Activate

    Path = ThisWorkbook.Path
    FileNm = Worksheets("Data Files").Cells(1, "J").Value

    Wb = Path & "\" & FileNm

    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-6],'[" & Wb & "]Product Receipt'!R2C1:R25C18,16,FALSE)"


    When checking the cell in the sheet itself, the following is what gets entered into the cell:

    =VLOOKUP(A3,'[C:\Users\rr140934\Documents\Excel Files\Production Files\Marshall\[1_MSH_ProductionSummary_Jun_28_2012 - Copy (2).xls]Product Receipt]1_MSH_ProductionSummary_Jun_28_'!$A$2:$R$25,16,FALSE)

    Rather than:

    =VLOOKUP(A3,'[C:\Users\rr140934\Documents\Excel Files\Production Files\Marshall\[1_MSH_ProductionSummary_Jun_28_2012 - Copy (2).xls]Product Receipt!$A$2:$R$25,16,FALSE)

    I have tried many different things to get this issue resolved and no help, any thoughts folks?

    Thank You

  2. #2
    Forum Contributor
    Join Date
    03-15-2007
    Location
    AL, USA
    MS-Off Ver
    2007
    Posts
    174

    Re: vlookup with variable for filename and path wants to append the filename after sheet n

    The way you are defining your path and filename is incorrect. You used an extra bracket ([) and missed an apostrope (')

    The result should be =VLOOKUP(A3,'C:\Users\rr140934\Documents\Excel Files\Production Files\Marshall\[1_MSH_ProductionSummary_Jun_28_2012 - Copy (2).xls]Product Receipt'!$A$2:$R$25,16,FALSE)

    here is a way to do it:

    Please Login or Register  to view this content.
    But there are multiple ways to take away a [ and add a ' lol.
    Last edited by mshale; 07-09-2012 at 03:11 PM. Reason: Added Sub Macro1() and an End Sub for easy copying

  3. #3
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: vlookup with variable for filename and path wants to append the filename after sheet n

    HA, I know what you are saying and this is one of the iterations I had used before posting the plead for help thread!! When I do it this way I get a RunTime error 1004 Application-defined or object-defined error. Should this be wrapped inside of a If on Error Resume Next statement?

  4. #4
    Forum Contributor
    Join Date
    07-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    139

    Re: vlookup with variable for filename and path wants to append the filename after sheet n

    Actually, I take that back, had 2 ,, next to each other, corrected that and this works just fine now. THANK YOU THANK YOU THANK YOU :>)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1