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?

wrote:
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?

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

Alan Beban

This could be indicative that tempquery has more than 5461 elements.

--
Regards,
Tom Ogilvy

" wrote in message
ups.com...
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?

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.

wrote:
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.

The limitation does exist in xl2000.

Alan Beban

wrote:
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.

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

wrote:
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.

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

" wrote in message
ups.com...
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.