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

[KR]

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.

[Lonnie M.]

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.

[Norman Jones]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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.
>>
>>
>
>