Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still Having Application.Match trouble
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still Having Application.Match trouble
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 " wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still Having Application.Match trouble
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 " wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with Index & Match | Excel Worksheet Functions | |||
application.match | Excel Discussion (Misc queries) | |||
Having trouble with vlookup and match | Excel Worksheet Functions | |||
Application.Match | Excel Programming | |||
Application.Match | Excel Programming |