How do I reference other files in VLookup?

[DarrellK]

I have been spinning my wheels trying to figure out why I get an error on the
second line of code below. This is not the actual code I want to use but
rather a modified version I wrote to try to isolate the problem. Here it is:

Cells(380, 1) = Application.WorksheetFunction.VLookup("X28.01",
Worksheets("Worksheet Totals").Range("F:CO"), 11 + j - 15, False)

Cells(382, 1) = Application.WorksheetFunction.VLookup("X28.01",
Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Worksheets("Worksheet
Totals").Range("F:CO"), 11 + j - 15, False)

I have two spreadsheets that I am using in the code above. The first line of
code looks for the value X28.01 in the spreadsheet in which I have written
the code and this line of code works just fine. The second line of code looks
for the value X28.01 in another spreadsheet "2006 Jan-Sep CAPEX Loadsheet
Actuals.xls" and it is with this line of code that I get a "Subscript out of
range" error.

As you can see, the two lines of code are exactly the same except for the
addition of 'Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").' in the
second line of code, which I thought is all that is needed to tell visual
basic which spreadsheet to use as the reference.

Note that both spreadsheets are in the same directory and have the same tabs
within them (i.e. they both have a "Worksheet Totals" tab). And there are
values in columns F through CO. And the problem occurs for my first iteration
of a loop with j=15 so there is no issue with the offset.

Any insight you might be able to provide me would be very much appreciated.

Also, I do want to eventually write this code so that it can reference a
spreadsheet on a network drive (S and if there are any issues you think I
should be aware of in referencing spreadsheets in other directories, I would
like to become aware of these issues as well.

Much Thanks.

Darrell

 

[DarrellK]

I tried to remove my previous question which has the Subject heading
"Subscript out of range" Error when using ... and replace it with this post
subject which I thought might be more descriptive but I was unable to remove
the other post once I posted this new item. Therefore, please disregard this
post.

Thanks.

Darrell