Match function, N/A error, how do I get around it?
 

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.

Match function, N/A error, how do I get around it?
 

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.

Match function, N/A error, how do I get around it?
 

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.

Match function, N/A error, how do I get around it?
 

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.