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
Bookmarks