Vlookup in VBA

[Guest]

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!

 

[Tom Ogilvy]

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

 

[Guest]

THANK YOU VERY MUCH!

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

 

[Guest]

with function like VLOOKUP and MATCH one can use the on error resume next
followed by whatever on error handle you had. For large blocks of code I
simply find it easier to use a function of my own to "wrap" the excel
function so that this looks after function errors and the error handling in
the caller becomes trivial
---------
result = SafeVLookup("blah", Range("data"), 7)
If result = "" Then
' handle it
Else
' use it
End If
----

Function SafeVLookup(what As String, _
where As Range, _
colChoice As Long) As String
On Error Resume Next
SafeVLookup = WorksheetFunction.VLookup(what, where, colChoice, False)