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!

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!

THANK YOU VERY MUCH!


"Tom Ogilvy" wrote:

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!

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)
On Error GoTo 0
End Function


"sharonm" wrote:

THANK YOU VERY MUCH!


"Tom Ogilvy" wrote:

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!