First, I'd use .formula instead of .value.

Second, it looks like you're missing a comma after the first parm.

ActiveCell.Formula _
= "=VLOOKUP(" & ActiveCell.Offset(0, -10).Address(False, False) _
& ",'[Transit Times.xls]Sheet1'!A:B,2,FALSE)"

(added a comma before the first apostrophe)

orquidea wrote:

Hi

I am using a Vloockup statement to get the information from 1 file to
another file. I have the string below, but it generates the error '1004':
Application-defined or object-defined error. I know the error is when I
added the file name [Transit Times.xls] because when I had the data for the
vloockup on sheet 1 in the same file were I was running the macro, it worked.
Could anyone help me please. FYI. In the macro I have the file Transit
Times open.

ActiveCell.Value = "=VLOOKUP(" & ActiveCell.Offset(0, -10).Address(False,
False) & "'[Transit Times.xls]Sheet1'!A:B,2,FALSE)"

Thanks,

--

Dave Peterson