Using IsError with Match

[Luke]

I have a list box from which a user may select a client. My code then looks
through a list of employees to find the ones belonging to that client. For
various reasons, it is possible to select a client that might not be
represented in the list of employees (it's necessary, I assure you). In case
the user selects a client that isn't in the employee list, I wanted to catch
this and avoid an error message. The code that is supposed to do this is
below.

If IsError(Application.WorksheetFunction.Match(ClientNum, ClientEEs, 0)) =
True Then
' Do stuff
End If

However, I still get the "Unable to get the Match property of the
WorksheetFunction class" error message. I don't really want to use On Error
Resume Next because, if the user selects a client not in the list of
employees, I've got a whole section of code I want to skip over.

How can I make this work so that it sees the error, sets a couple of
variables equal to 0, then continues at a different point in the code?

Thanks for any help.

 

[Bob Phillips]

That should work. It can be shortened to

If IsError(Application.Match(ClientNum, ClientEEs, 0)) Then
' Do stuff
End If

but that is essentially the same.

You could use Onerror like so

On Error Resume Next
idx =Application.Match(ClientNum, ClientEEs, 0))
On Error Goto 0
If idx > 0 Then
'Do stuff
End If

 

[Dave Peterson]

Drop the .worksheetfunction portion:

if IsError(Application.Match(....

 

[Luke]

Worked like a charm. Thank you very much.

If you don't mind, though, can you tell me why that made a difference?

Thanks.

 

[Dave Peterson]

Just the way Microsoft implemented it (same with =vlookup(), too).

dim res as variant
on error resume next
res = application.worksheetfunction.match(...)
if err.number <> 0 then
'an error
err.clear
res = "error"
end if
on error goto 0

Would be an alternative way of using application.worksheetfunction or just
worksheetfunction.