Application.Match Type mismatch error

  • Thread starter Thread starter marston.gould
  • Start date Start date
M

marston.gould

I'm trying to use the Application.Match call using two arrays.

The exact line is:

e = Application.Match(query(i,0),tempquery,0)

query(i,0) holds a Variant/String that looks something like

"100010300106800000"

tempquery is an array holding a series
of similar values as strings.

I supposed I could convert them all to
values, but I think it drops trailing or leading
zeros and that could screw up the match as well.

Any suggestions?
 
I'm trying to use the Application.Match call using two arrays.

The exact line is:

e = Application.Match(query(i,0),tempquery,0)

query(i,0) holds a Variant/String that looks something like

"100010300106800000"

tempquery is an array holding a series
of similar values as strings.

I supposed I could convert them all to
values, but I think it drops trailing or leading
zeros and that could screw up the match as well.

Any suggestions?
Click to expand...
It isn't clear that there is any problem; what's the question? Is it
that tempquery is more than 1-dimensional?

Alan Beban
 
Tempquery is only one dimensional. And it contains about 12000 unique
items in the list.
The only thing I could think of is that in the line e is dimensioned as
a long while the items in query and tempquery are
variant/strings.....but that shouldn't matter I would think...
 
I thought this limitation didn't exist in xl2000 and beyond....?
If it does, I guess I could break the tempquery into batches 5461
elements long (plus some leftover) and do the match that way.
 
I thought this limitation didn't exist in xl2000 and beyond....?
If it does, I guess I could break the tempquery into batches 5461
elements long (plus some leftover) and do the match that way.
Click to expand...
The limitation does exist in xl2000.

Alan Beban
 
I thought this limitation didn't exist in xl2000 and beyond....?
If it does, I guess I could break the tempquery into batches 5461
elements long (plus some leftover) and do the match that way.
Click to expand...
You didn't ask for this, but here's some sample code that might do what
you suggest; it uses the SubArray function.

query = Array("100010300106800000", "x", "y")
Dim tempquery
ReDim tempquery(1 To 12000)
For i = 1 To 12000
tempquery(i) = 1
Next
tempquery(10000) = "100010300106800000"
Dim tq
ReDim tq(1 To 3)
z = 1
For i = 1 To 3 'This loop loads tq with 3 4000-element chunks
tq(i) = SubArray(tempquery, 1 + 4000 * (i - 1), 4000 * i, 1, 1)
Next
On Error Resume Next
For i = 1 To 3
x = Application.Match(query(1), tq(i), 0)
If TypeName(x) <> "Error" Then Exit For
Next
x = x + (i - 1) * 4000
Debug.Print x

Alan Beban
 
I thought this limitation didn't exist in xl2000 and beyond....?
If it does, I guess I could break the tempquery into batches 5461
elements long (plus some leftover) and do the match that way.
Click to expand...
Or

For j = LBound(tempquery) To UBound(tempquery) - LBound(tempquery) + 1
If tempquery(j) = query(i,0) Then Exit For
Next
x = j

That seems to take about 20 milliseconds on my machine to search through
the entire array for a match, about 5 milliseconds to search one-quarter
of the way through.

Alan Beban
 
you need to dim e as variant to catch the errorvalue when there is no match
: otherwise you will get a type mismatch error


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com
 
Back
Top