Application.Match
 

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?

Application.Match
 

Application.Match
 

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.


On 1 Sep 2004 16:07:44 -0700, "
wrote:

I just checked again -

the item to be matched is a Variant/String and the
array coming out of ColumnVector is also a Variant/String

The odd thing is when I do a "watch" on Column Vector I see the
folowing

(-) ColumnVector Variant/Variant(1 to
20000,1 to 1)
(-) ColumnVector(1) Variant(1 to 1)
ColumnVector(1,1) "100" Variant/String
(+) ColumnVector(2)
.
.
.



(+) ColumnVector(20000)

Is it possible that the Match is having problem because it sees the
item to be matched as a Varient/String and the columnvector as
Variant(1 to 1)

Application.Match
 

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.

--
Regards,
Tom Ogilvy

"Myrna Larson" wrote in message
...
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.


On 1 Sep 2004 16:07:44 -0700, "
wrote:

I just checked again -

the item to be matched is a Variant/String and the
array coming out of ColumnVector is also a Variant/String

The odd thing is when I do a "watch" on Column Vector I see the
folowing

(-) ColumnVector Variant/Variant(1 to
20000,1 to 1)
(-) ColumnVector(1) Variant(1 to 1)
ColumnVector(1,1) "100" Variant/String
(+) ColumnVector(2)
.
.
.



(+) ColumnVector(20000)

Is it possible that the Match is having problem because it sees the
item to be matched as a Varient/String and the columnvector as
Variant(1 to 1)

Application.Match
 

Tom Ogilvy wrote:
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

Application.Match
 

Alan Beban wrote:

Tom Ogilvy wrote:

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