When you refer to a workbook with:

Workbooks("workbooknamehere.xls")...

You don't include the path. So in your case:

set wb = workbooks("validation macro.xls")

And that workbook has to be open, too.

sharonm wrote:

Hello,
I am trying to use Vlookup in Excel VBA. I have two sheets in my workbook
which is called "Validation Macro.xls" . I want to do a lookup based on a
cell's value in the first sheet. The lookup range is in the second sheet
called "TLA". I am trying to do this with the code below. Only the first line
gives me the following error: "Runtime error 9 - Subscript out of range".
Would anyone have any suggesions on how to properly do this? Thanks in
advance!

Set wb = Workbooks("C:\Documents and Settings\Sharon\MyDocuments\Validation
Macro.xls")
Set ws = wb.Sheets("TLA")
Set rng = ws.Range("A2:D15")

ActiveCell.Value =
Application.WorksheetFunction.VLookup(ActiveCell.O ffset(0, -31).Value, rng,
3, False)

--

Dave Peterson