Application.Match

[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?

 

[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.

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?

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

 

[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.

 

[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.

 

[Alan Beban]

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.

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

 

[Alan Beban]

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

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