If you qualify Vlookup with WorksheetFunction, then failure to match will
results in a 1004 error (this is by design).

If you qualify Vlookup with Application instead, you can use the IsError
test.

Set wb = Workbooks("TestWorkbook.xls")
Set ws = wb.Sheets("DeptLookup")
Set WS2 = wb.Sheets("MainData")
Set rng = ws.Range("$A$3:$C$59")
Dim y As Variant

y = Application.VLookup(ActiveCell.Offset(0, -31).Value, rng, 1, False)
If Not IsError(y) Then
ActiveCell.Offset(0, -34).Value = y
End if


--
Regards,
Tom Ogilvy



"sharonm" wrote in message
...
I am trying to do a Vlookup. My lookup value is on the MainData sheet and
my
lookup range is on the DeptLookup sheet. If there is a match, the code
works
fine. However, if there is no match, I get a Runtime error '1004' -
Application defined or object defined error on the line

y = WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value, rng, 1,
False

My code is:
Set wb = Workbooks("TestWorkbook.xls")
Set ws = wb.Sheets("DeptLookup")
Set WS2 = wb.Sheets("MainData")
Set rng = ws.Range("$A$3:$C$59")
Dim y As Variant

y = WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value, rng, 1,
False)
If Not IsError(y) Then
ActiveCell.Offset(0, -34).Value =
Application.WorksheetFunction.VLookup_(ActiveCell. Offset(0, -31).Value,
rng,
1, False)

Would anyone be able to tell me what I am doing wrong?

Thanks in advance!