In XL 2003 VBA, I am using the [application.worksheetfunction] Match
function to identify the position of a variable within another array. I am
requiring an exact match.

I just had my first instance where the match was not found anywhere, and per
the help file:

a.. If MATCH is unsuccessful in finding a match, it returns the #N/A error
value.

So in my code I tried to get around this with an If statement:

If IsError (my match statement) then
'do nothing
Else
'here is all my old code
End if

but it isn't catching the #N/A as an error, and I don't see any similar
operators (?) that would catch the #N/A.

Is there something else I can use? I don't want to use a generic "on error
resume next" for the whole procedure, because I want to catch any other
errors that might occur. I also have to make sure the rest of my code
_doesn't_ run if the match isn't found, otherwise it will cause all sorts of
other errors.

I welcome and appreciate any advice or solutions you might have,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Hi, you may want to use an 'On Error Resume Next' statement. It aslso
might be worth taking a look at the vba function 'InStr'.
HTH--Lonnie M.

Hi KR.

Try using Application.Match and an On Error statement. Simply restore
default error handlimg after the Match statement.

For example:

Sub sTester()
Dim arr As Variant
Dim res As Variant
Dim sStr As String

sStr = "YourSearchString"

arr = Array("Bill", "Ben", "John", "Anne")

On Error Resume Next
res = Application.Match(sStr, arr, 0)
On Error GoTo 0

If Not IsError(res) Then
MsgBox res
Else
MsgBox """" & sStr & """ not found"
End If

End Sub


---
Regards,
Norman



"KR" wrote in message
...
In XL 2003 VBA, I am using the [application.worksheetfunction] Match
function to identify the position of a variable within another array. I am
requiring an exact match.

I just had my first instance where the match was not found anywhere, and
per
the help file:

a.. If MATCH is unsuccessful in finding a match, it returns the #N/A error
value.

So in my code I tried to get around this with an If statement:

If IsError (my match statement) then
'do nothing
Else
'here is all my old code
End if

but it isn't catching the #N/A as an error, and I don't see any similar
operators (?) that would catch the #N/A.

Is there something else I can use? I don't want to use a generic "on error
resume next" for the whole procedure, because I want to catch any other
errors that might occur. I also have to make sure the rest of my code
_doesn't_ run if the match isn't found, otherwise it will cause all sorts
of
other errors.

I welcome and appreciate any advice or solutions you might have,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Hi KR,

Correcting my post, the On Error statement is not required with the adopted
Application.Match, although error handling would be required with
Application.WorksheetFunction.Match.


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi KR.

Try using Application.Match and an On Error statement. Simply restore
default error handlimg after the Match statement.

For example:

Sub sTester()
Dim arr As Variant
Dim res As Variant
Dim sStr As String

sStr = "YourSearchString"

arr = Array("Bill", "Ben", "John", "Anne")

On Error Resume Next
res = Application.Match(sStr, arr, 0)
On Error GoTo 0

If Not IsError(res) Then
MsgBox res
Else
MsgBox """" & sStr & """ not found"
End If

End Sub


---
Regards,
Norman



"KR" wrote in message
...
In XL 2003 VBA, I am using the [application.worksheetfunction] Match
function to identify the position of a variable within another array. I
am
requiring an exact match.

I just had my first instance where the match was not found anywhere, and
per
the help file:

a.. If MATCH is unsuccessful in finding a match, it returns the #N/A
error
value.

So in my code I tried to get around this with an If statement:

If IsError (my match statement) then
'do nothing
Else
'here is all my old code
End if

but it isn't catching the #N/A as an error, and I don't see any similar
operators (?) that would catch the #N/A.

Is there something else I can use? I don't want to use a generic "on
error
resume next" for the whole procedure, because I want to catch any other
errors that might occur. I also have to make sure the rest of my code
_doesn't_ run if the match isn't found, otherwise it will cause all sorts
of
other errors.

I welcome and appreciate any advice or solutions you might have,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.