Application.Match

M

marston.gould

I'm trying to use

var = Application.Match(value,arr,0) and
var2 = Application.Match(value,arr)

but I notice that when I go to type in the
formula in VBA, I'm not seeing .Match as a choice.
When I run the code I'm getting a type mismatch...

Thoughts?
 
A

Alan Beban

I'm trying to use

var = Application.Match(value,arr,0) and
var2 = Application.Match(value,arr)

but I notice that when I go to type in the
formula in VBA, I'm not seeing .Match as a choice.
Click to expand...

I don't understand the previous two lines; when you go to type in what
formula where, and where are you expecting to see .Match as a choice?
When I run the code I'm getting a type mismatch...

Thoughts?
Click to expand...
It's difficult to tell without knowing what arr looks like and without
knowing what value is. But it might be that value in the above is
something like "11", and the Match function is looking for 11.

Alan Beban
 
M

Myrna Larson

If you have the statement

Dim ColumnVector(1 to 20000, 1 to 1)

this is a 2-dim array. The Dim statement for a 1-Dim array looks like this:

Dim ColumnVector(1 To 20000)

How did you load ColumnVector? From a worksheet? If so, it should work.
 
T

Tom Ogilvy

What version of Excel Marston? As stated in response to a previous
question, if using Excel 2000 or earlier, Application.Match will not work
with an array that has more than 5461 elements. And the response/error it
Type Mismatch.
 
A

Alan Beban

Tom said:
What version of Excel Marston? As stated in response to a previous
question, if using Excel 2000 or earlier, Application.Match will not work
with an array that has more than 5461 elements. And the response/error it
Type Mismatch.
Click to expand...

There's no problem with Application.Match; I think you may be recalling
a problem with Application.Index.

I.e., Application.Match(value,Index(arr,0,1),0) won't work in xl2000 for
arr more than 5461 elements. But

Application.Match(value,ColumnVector(arr,1),0) will because it doesn't
depend on the Index function.

Alan Beban
 
A

Alan Beban

Alan said:
There's no problem with Application.Match; I think you may be recalling
a problem with Application.Index.

I.e., Application.Match(value,Index(arr,0,1),0) won't work in xl2000 for
arr more than 5461 elements. But

Application.Match(value,ColumnVector(arr,1),0) will because it doesn't
depend on the Index function.

Alan Beban
Click to expand...
Bite my tongue, Tom. Application.Match *is* subject to the 5461 element
limit.

I tested on a 2 column range. E.g.,
arr=Range("a1:b2730")
x=Application.Match(value,Application.Index(arr,0,1),0)
This works, but fails when 2730 is change to 2731 because *arr* then has
5462 elements.
x=Application.Match(value,ColumnVector(arr,1),0) works for 2730 *and*
for 2731, which led me to my wrong conclusion; it works until the 2730
is increased beyond 5461 and then fails because the *column* has more
than the 5461 element limit.

My apologies,
Alan Beban
 
Top