How can I trap this Vlookup error in a VBA macro

[Richard Buttrey]

Hi,

I have a need to identify whether a Job Number appears in a specific
range, and take different actions in a mcaro dependent on whether it
is or isn't present.

As a function this would simply be

=If(Iserror( Vlookup(x,MyRange,1,false)),"This","That")

However I'm having difficulties building this into a macro using
Application.WorksheetFunction. I keep stumbling on the 'Unable to get
the Vlookup property of the Worksheet Function Class" where the job
number doesn't exist

I've also tried MyRange.Find(what:=JobNo, after ............etc.,
but get the 'Object Variable with Block Variable not set' error, where
the jobs doesn't exist.

How can I construct an

If "JobNo exists" Then
"This"
else
"That"
End If

test?


Usual TIA

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Carim]

Hi Richard,

Is your "JobNo" structured in a particular way ?

Carim

 

[Ardus Petus]

Strange indeed. Get around with on error:

Sub test()
Dim i As Long
On Error Resume Next
i = WorksheetFunction.Match(9, Range("A1:A12"), 0)
On Error GoTo 0
If i = 0 Then MsgBox "Not Found"
End Sub

HTH

 

[Carim]

Richard,

Could the following help ...
Result = Application.VLookup(x, MyRange,2,0)
if iserror(Result) then

to detect where somthing is going wrong ...

HTH
Carim

 

[Bondi]

Hi Richard,
In another thread some time ago i found that VLookup() produces a
runtime error when the value can not be found. You can use the
following to snap that event.
Code:

On Error Resume Next

'VLookUp()

If Err.Number <> 0 Then
'If not found
Debug.Print Err.Number
Debug.Print Err.Description
Else
' If found
End If
On Error Goto ErorrHndl

Regards,
Bondi

 

[Carim]

Hi Richard,

An alternative to your test could be :
If cell Like "[A-Z]####" Then
once the pattern is identified or not, do This or That ...

HTH
Cheers
Carim

 

[Tom Ogilvy]

Here is a summary (for vlookup, but also for Match and probably for all the
other lookup functions)

Application.WorksheetFunction.Vlookup(args)
WorksheetFunction.Vlookup(args)

both raise a trappable error (1004 error) when the value is not found

Application.Vlookup(args)

This can be controlled or accounted for with with normal error handling such
as
On Error Resume next
or
On error goto Label


returns the equivalent of #N/A and can be checked with IsError

dim res as Variant
res = Application.Vlookup(args)
if iserror(res) then
' value was not found
else
' value was found
End if

When using find, use a construct like

dim rng as Range

set rng = range.Find(value)
if not rng is nothing then
' rng holds a reference to the found cell
else
' the search value was not found
End if

 

[Doug Glancy]

Carim,

That's the way to go. Notice that it works only if you use
Application.VLookup. If you use WorksheetFunction.VLookup you will get your
original runtime error.

hth,

Doug

 

[Richard Buttrey]

Hi Richard,
In another thread some time ago i found that VLookup() produces a
runtime error when the value can not be found. You can use the
following to snap that event.
Code:

On Error Resume Next

'VLookUp()

If Err.Number <> 0 Then
'If not found
Debug.Print Err.Number
Debug.Print Err.Description
Else
' If found
End If
On Error Goto ErorrHndl

Regards,
Bondi

Hi Bondi

Thanks for that suggestion, I hadn't thought of trapping the error
number and using that - rather neat.

It also avoids the problem of forcing the program flow to an error
handler, which by definition is outside any Looping part of the
Procedure.

Regards

PS, thanks also to the others who responded.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________