Returning the index of an array value?

[chemdude77]

If I have a 2D variant array with the following values:

Array(1,1) = 3.1 Array(2,1) = 0
Array(1,2) = 2.2 Array(2,2) = 3.5
Array(1,3) = 0 Array(2,3) = 0
Array(1,4) = 0 Array(2,4) = 2.2

I want to print out a term depending on what column of the array contains a
non-zero value. So I want to print out "A" if there is a value in column 1,
"B" if a value in column 2, "C" if a value in column 3, "D" if a value in
column 4, etc.. So in the example above I would get A and B (since 1,1 and
1,2 have nonzero values) in separate cells on my worksheet and then B and D
(from 2,2 and 2,4) in the next column on the worksheet in separate cells. I
hope I explained this well enough Any way to do this?

I would also like the values to be printed out in order based on the
descending values in the array, but this is probably asking a lot? Please
help!
Matt

[Robert Mulroney]

What do you want this for? If you're going to use the result letter to refer
to a column name in an excel spread sheet it would be easier for you to refer
to the column by it's index. ie

ActiveSheet.Columns(2).Select 'Selects all of Column B
' - or -
ActiveSheet.Cells(3, 2).Select 'Selects the cell $B$3 - (row, column)

Otherwise I suppose you could have one long Case clause

Select Case x
Case is = 1
y = "A"
Case is = 2
y = "B"
Case is = 3
y = "C"
'.........
End Select

That's pretty lazy coding though. And have you thought about what you're
going to do at index 27?

- Rm



"chemdude77" wrote:

> If I have a 2D variant array with the following values:
>
> Array(1,1) = 3.1 Array(2,1) = 0
> Array(1,2) = 2.2 Array(2,2) = 3.5
> Array(1,3) = 0 Array(2,3) = 0
> Array(1,4) = 0 Array(2,4) = 2.2
>
> I want to print out a term depending on what column of the array contains a
> non-zero value. So I want to print out "A" if there is a value in column 1,
> "B" if a value in column 2, "C" if a value in column 3, "D" if a value in
> column 4, etc.. So in the example above I would get A and B (since 1,1 and
> 1,2 have nonzero values) in separate cells on my worksheet and then B and D
> (from 2,2 and 2,4) in the next column on the worksheet in separate cells. I
> hope I explained this well enough Any way to do this?
>
> I would also like the values to be printed out in order based on the
> descending values in the array, but this is probably asking a lot? Please
> help!
> Matt

[chemdude77]

Hi Robert,

Thanks so much for your reply.

My problem was a little hard to explain. I am not wanting to return the
actual Excel sheet column. Instead the column the data is in has a meaning so
I am trying to get if its in column A then "resultA" is returned, if B then
"resultB" is returned.

I ended up using a function to do this, so that column = FunctionX(2) would
return the result I want, where 2 is the column number. Hope I explained this
well enough. Anyway the problem is solved, thanks for your help.

Matt

"Robert Mulroney" wrote:

>
> What do you want this for? If you're going to use the result letter to refer
> to a column name in an excel spread sheet it would be easier for you to refer
> to the column by it's index. ie
>
> ActiveSheet.Columns(2).Select 'Selects all of Column B
> ' - or -
> ActiveSheet.Cells(3, 2).Select 'Selects the cell $B$3 - (row, column)
>
> Otherwise I suppose you could have one long Case clause
>
> Select Case x
> Case is = 1
> y = "A"
> Case is = 2
> y = "B"
> Case is = 3
> y = "C"
> '.........
> End Select
>
> That's pretty lazy coding though. And have you thought about what you're
> going to do at index 27?
>
> - Rm
>
>
>
> "chemdude77" wrote:
>
> > If I have a 2D variant array with the following values:
> >
> > Array(1,1) = 3.1 Array(2,1) = 0
> > Array(1,2) = 2.2 Array(2,2) = 3.5
> > Array(1,3) = 0 Array(2,3) = 0
> > Array(1,4) = 0 Array(2,4) = 2.2
> >
> > I want to print out a term depending on what column of the array contains a
> > non-zero value. So I want to print out "A" if there is a value in column 1,
> > "B" if a value in column 2, "C" if a value in column 3, "D" if a value in
> > column 4, etc.. So in the example above I would get A and B (since 1,1 and
> > 1,2 have nonzero values) in separate cells on my worksheet and then B and D
> > (from 2,2 and 2,4) in the next column on the worksheet in separate cells. I
> > hope I explained this well enough Any way to do this?
> >
> > I would also like the values to be printed out in order based on the
> > descending values in the array, but this is probably asking a lot? Please
> > help!
> > Matt