In a macro in Excel 2002 I have this:

With DestSh
.Range("F2", Cells(LastR, 6)).Value = _
"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Rese arch\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With

It works. It populates column F with that formula, and the formula
works to look up stock names from the symbols in column E. This
works even though the lookup workbook is closed. However, the
macro pulls up a Windows Explorer Window set to that directory.
The workbook I want is highlighted, but Excel is waiting for me
to click on the book or hit Enter. Then the macro finishes.

I don't know why Excel is asking me for confirmation. I'd like it
to just work without a pause. Am I going about this the right way?
How can I stop the required interaction from me?

Also, I tried to set a string var in my macro to the long name so
I could use the var instead in the statement, but it doesn't work.
I can basically understand why it won't work, but I'd like to
know a reasonable approach. Oh! It dawns on me that I could
set a name in the worksheet and just use that in the macro. Hmmmmm.

Second question about this: some of the lookups fail with an #NA
error. I expected that. In the case of these errors, I want to
set those cells to a different VLOOKUP statement instead. I
don't know how to get my macro to do such a thing. (The second
VLOOKUP would be similar, but would start in column B instead of A.)

I'd also like to know how I can find the last row in my lookup
table. There are actually 2900-something entries in it now.
I don't need to say $A$2:$C$4000, but would rather say
$A$2 to lastrow in column C. Any ideas here would also
be most appreciated.

dman