Aha. Then you need to break up the string as well as write the lookup table
reference in R1C1 format:

"=VLOOKUP(RC[-1],'[" & fDate & "mrp.xls]Sheet1'!R1C3:R4C3,4,FALSE)"

--

Vasant

"Jill" wrote in message
...
Oops...Sorry,
The formula is
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[fDate & mrp.xls]Sheet1'!
C1:C4,4,FALSE)"

-----Original Message-----
I must be missing something. If the worksheet is called
0618mrp.xls, why is
the formulqa referring to IDA001-Intransit.xls?

--

Vasant

"Jill" wrote in
message
...
Hello,
I need some help with this formula. I'm trying to do a
vLookup from my active sheet to another open
spreadsheet.
The other spreadsheet has been named 0618mrp.xls. The
mmdd (06/18) has been defined earlier in the macro as
fDate. The macro will effectively open the file using
fDate & mrp.xls. The problem is when I use fDate &
mrp.xls in a vLookup Excel opens a window and wants me
to
select a file to refer to. The file I want to refer to
is already open but Excel won't recognize it in the
vLookup. This is the formula:
ActiveCell.CurrentRegion.Columns(3).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[IDA001-Intransit.xls]Sheet1'!
R1C1:R19C2,2,FALSE)"

I would really appreciate your help,
Jill


.