Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning the index of an array value?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning the index of an array value?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning the index of an array value?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match and index returning N/A | Excel Worksheet Functions | |||
Index returning #N/A | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Returning an array from the INDEX function | Excel Worksheet Functions | |||
HELP: Returning a value in an index | Excel Worksheet Functions |