Match function not wokring on 2D array

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

 

[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

 

[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

 

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

 

[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

 

[Alan Beban]

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

 

[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

 

[Alan Beban]

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