Using variable in VLOOKUP

[lday75]

I am having issues with using a variable in a vlookup that points to my file and path. I have the following set up:

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

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)"

The problem is that the cells get #NA as if the values are not found.

Any thoughts on this please?

[Pete_UK]

What formula (as shown in the formula bar) is actually put into the active cell?

Pete

[lday75]

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

[lday75]

Oh wait, correction, it is - =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)

hmmm

[Pete_UK]

Well, maybe you have mis-typed that, as you shouldn't have a closed square bracket ] in the sheetname. Assuming that the formula is correct, then you need to examine why you are getting #N/A. You seem to think that there are matching values in the other file, so you need to look at these more carefully - if they are text values then do you have extra or fewer spaces in A3 compared with the cell you think it matches with? (you can use LEN on each cell to find out how many characters there are). If these are numeric values then could it be that in one file they are actually text values that happen to look like numbers?

Hope this helps.

Pete

[lday75]

Correct, the closed square bracket should not be there, however I am not sure where it is coming from. I know that the values exists due to this being my second version of this macro. I had the previous code which works fine, I am just trying to include the entire path + filename into a variable with this version.

Previous Code (which returns values)

=VLOOKUP(RC[-6],'C:\Users\rr140934\Documents\Excel Files\Production Files\Marshall\[" & FileNm & "]Product Receipt'!R2C1:R25C18,16,FALSE)

enter this into cells

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