That means the value wasn't found in range. One approach is to skip the
worksheetfunction part. Then you can check with iserror

res = Application.Vlookup(ActiveCell.Offset(0,-31).value,rng,3,False)
if iserror(res) then
msgbox ActiveCell.offset(0,-31).Value & " was not found"
else
ActiveCell.Value = res
end if

--
Regards,
Tom Ogilvy

"sharonm" wrote in message
...
Thanks to all. But now when on the line with the lookup-

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

I get the error 1004: Application-defined or Object-defined error

Any Suggestions?


"Dave Peterson" wrote:

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