VBA VLookup Problem

[Chris]

I'm trying to run a macro that uses a vlookup that is getting the
lookup value from cell J2 of Book1.xlsx (which was just created
through a macro), and searching a table array in My_Book.xlsx, which
is saved to my desktop.

MyBook.Sheets(1).Range("J4").Formula = "=VLOOKUP(J2,[My_Book.xlsx]
Calendar!L6:M50,2,FALSE)"

Exel opens a dilaog box titled Update Values: My_book.xlsx and waits
for me to locate the file and click ok. If I do this the macro
continues as expected. How do I stop the dialog box from opening, and
have it automatically find the book? I have tried entering the full
path to the file, and have opened the My_book sheet before running the
macro.

 

[Dave Peterson]

I'm not sure I understand...

But the formula you're assigning to J4 is written as if my_book.xlsx is open.
Is that the case?

If no, then either open it in your code or write the formula so that it includes
the drive and path.

I'm trying to run a macro that uses a vlookup that is getting the
lookup value from cell J2 of Book1.xlsx (which was just created
through a macro), and searching a table array in My_Book.xlsx, which
is saved to my desktop.

MyBook.Sheets(1).Range("J4").Formula = "=VLOOKUP(J2,[My_Book.xlsx]
Calendar!L6:M50,2,FALSE)"

Exel opens a dilaog box titled Update Values: My_book.xlsx and waits
for me to locate the file and click ok. If I do this the macro
continues as expected. How do I stop the dialog box from opening, and
have it automatically find the book? I have tried entering the full
path to the file, and have opened the My_book sheet before running the
macro.