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]

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