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
__________________________

Hi Richard,

Is your "JobNo" structured in a particular way ?

Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=531155

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
--
AP

"Richard Buttrey" a écrit dans le
message de ...
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
__________________________

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

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

On Sat, 8 Apr 2006 03:22:45 -0500, Carim
wrote:


Hi Richard,

Is your "JobNo" structured in a particular way ?

Carim

It's always 5 characters, with an initial alpha and four numerics if
that helps.

e.g. M0123

Thanks

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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

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



--
Regards,
Tom Ogilvy


"Carim" wrote in message
oups.com...
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

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


"Carim" wrote in message
oups.com...
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

On 8 Apr 2006 01:50:15 -0700, "Bondi" wrote:

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
__________________________