Using IsError with Match
 

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(Client Num, 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.

Using IsError with Match
 

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


--

HTH

Bob

"Luke" wrote in message
...
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(Client Num, 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.

Using IsError with Match
 

Drop the .worksheetfunction portion:

if IsError(Application.Match(....



Luke wrote:

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(Client Num, 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.

--

Dave Peterson

Using IsError with Match
 

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" wrote:

Drop the .worksheetfunction portion:

if IsError(Application.Match(....



Luke wrote:

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(Client Num, 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.

--

Dave Peterson
.

Using IsError with Match
 

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.



Luke wrote:

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" wrote:

Drop the .worksheetfunction portion:

if IsError(Application.Match(....



Luke wrote:

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(Client Num, 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.

--

Dave Peterson
.


--

Dave Peterson