Still Having Application.Match trouble

[marston.gould]

Can someone explain to me why the first of these two examples works,
but the second doesn't?

Example 1:

Sub Test
Dim i As Integer
Dim v, arr(999) As String
Dim u,l As Long
For i = 0 to 9
arr(i) = "00" & CStr(i)
Next i
For i = 10 to 99
arr(i) = "0" & CStr(i)
Next i
For i = 100 to 999
arr(i) = Cstr(i)
Next i
v = "600"
l = Application.Match(v,arr,0)
u = Application.Match(v,arr)
End Sub

Example 2

Assume that there are a set of numbers as strings in cells A1 - A1000

Sub Test
Dim i As Integer
Dim v, arr(999) As String
Dim u,l As Long
For i = 1 to 1000
arr(i-1) = CStr(Range("A1").Offset(i-1,0).Value)
v = CStr(Range("A1").Offset(600,0).Value)
l = Application.Match(v,arr,0)
u = Application.Match(v,arr)
End Sub

In the case of the first example, everything works, in the case of the
second (which is simplified from my real code), I'm getting a type
mismatch error.

I've done a "Watch" on both the item to be matched and the single
column array and in both cases each and every item in both is a String.

 

[Charles Williams]

application.match will return a variant containing an error value if the
equivalent Match returns #N/A etc.
So you should always assign the result to a variant and then test the
variant using ISERROR

Also Dim u,l As Long does not dim both u and l as long, it dims u as variant
and l as long.

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Andrew]

I tested your function and got it to work fine. However, if there is
no match (eg. if v="7" and there is no "7" in the range) then I get a
type mismatch error. Since you are using a workbook function this
would return #N/A if you were using it there.

Two possible ideas:
1) trap this error and handle as appropriate
2) Use a loop to evaluate each variable in your array (not necessarily
slower since I have found that calling workbook functions can also be
relatively slow)

Cheers,
Andrew