Match function not wokring on 2D array

E

ExcelMonkey

What is wrong here? I have a 2D array. The following gives me an error say
it can't find Match property. I am trying to locate first row with 0 in a
sorted array (high to low)

MaxRow = Application.WorksheetFunction.Match(0, ConsFunctionArray, 1)

Yet I know that my first zero value occurs in row 5 of the second dimension
in hte 0-based array

?ConsFunctionArray(4,1)
0

As such I am expectinge th results to give me a 4 above.
 
R

RB Smissaert

I don't think it works on 2-D arrays as how would it know what column to
look in? There is no column argument in the function.
As your array is sorted you could do a binary search or use VLookup.

RBS
 
E

ExcelMonkey

What I am trying to do is redim and array. I have a 2D array with values in
the second column. They array is sorted in descending order. I want to
redim the array so to get rid of all rows with 0 values in the first column.
I was using the match to find the row with the first 0 and use this to redim
the array.

Thanks

EM
 
N

Niek Otten

Just to repeat RBS; MATCH() only works on a one-dimensional array. I agree HELP is not very clear about this, but still, that's
how it is


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| What I am trying to do is redim and array. I have a 2D array with values in
| the second column. They array is sorted in descending order. I want to
| redim the array so to get rid of all rows with 0 values in the first column.
| I was using the match to find the row with the first 0 and use this to redim
| the array.
|
| Thanks
|
| EM
|
| "RB Smissaert" wrote:
|
| > I don't think it works on 2-D arrays as how would it know what column to
| > look in? There is no column argument in the function.
| > As your array is sorted you could do a binary search or use VLookup.
| >
| > RBS
| >
| > | > > What is wrong here? I have a 2D array. The following gives me an error
| > > say
| > > it can't find Match property. I am trying to locate first row with 0 in a
| > > sorted array (high to low)
| > >
| > > MaxRow = Application.WorksheetFunction.Match(0, ConsFunctionArray, 1)
| > >
| > > Yet I know that my first zero value occurs in row 5 of the second
| > > dimension
| > > in hte 0-based array
| > >
| > > ?ConsFunctionArray(4,1)
| > > 0
| > >
| > > As such I am expectinge th results to give me a 4 above.
| >
| >
 
R

RB Smissaert

Just run a loop either lbound to ubound or vice-versa, depending on
what you expect to get your answer the quickest. Once you got the answer
you can do an Exit For.

RBS
 
A

Alan Beban

ExcelMonkey said:
. . .
Yet I know that my first zero value occurs in row 5 of the second dimension
in hte 0-based array

Do you mean row 5 of the second column? If so, what do you mean in your
later post "get rid of all rows with 0 values in the first column"? How
would finding the first 0 value in the second column help you get rid of
rows with 0 in the first column???

If you know the column in which you want to find the first 0, let's say
column 4, you can use

Application.Match(0, Application.Index(ConsFunctionArray, 0, 4), 0) - 1

Alan Beban
 
E

ExcelMonkey

Yes I did mean row 5 of the second column. Slipped a keystroke when typing.
Sorry for the confusion. Tried your suggestion. I am getting a Type
mismatch error. I know the following: The Array has been sorted in
descending order based on column 2 and the fist zero value shows up in row 5
of the zero based array

?ConsFunctionArray(4,1)
0

When I try just Application.Index(ConsFunctionArray, 0, 4)
I get an Error 2023.

Wondering what is going on here. Will keep looking.

Thanks

EM
 
A

Alan Beban

ExcelMonkey said:
Yes I did mean row 5 of the second column. Slipped a keystroke when typing.
Sorry for the confusion. Tried your suggestion. I am getting a Type
mismatch error. I know the following: The Array has been sorted in
descending order based on column 2 and the fist zero value shows up in row 5
of the zero based array

?ConsFunctionArray(4,1)
0

When I try just Application.Index(ConsFunctionArray, 0, 4)
I get an Error 2023.

Then ConsFunctionArray doesn't have a 4th column. The illustration I
provided above explicitly assumed you were seeking in the 4th column. If
you are seeking in the 2nd column then of course you would use

Application.Index(ConsFunctionArray, 0, 2)

Alan Beban
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top