Vlookup in VBA

[sharonm]

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


--
Regards,
Tom Ogilvy



"sharonm" <[email protected]> wrote in message
news:[email protected]...
> 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!

[sharonm]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

[Patrick Molloy]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> >
> >
> >