Match function not wokring on 2D array
 

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.

Match function not wokring on 2D array
 

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

Match function not wokring on 2D array
 

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

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

Match function not wokring on 2D array
 

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

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

Match function not wokring on 2D array
 

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


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

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

Match function not wokring on 2D array
 

ExcelMonkey wrote:
. . .
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

Match function not wokring on 2D array
 

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" wrote:

ExcelMonkey wrote:
. . .
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

Match function not wokring on 2D array
 

ExcelMonkey wrote:
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.

"Alan Beban" wrote:
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

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