Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a column based on a index
I want to be able to select several columns based on a number in row 6.
These selections will be copied out to another sheet to get the average value and based on these datas make a chart. I know how to select the index numbers and based on their value change their color index. The next step is to, based on these color indexes, select the range with all the numbers in their column. 1 2 1 2 3 1 2 % % % % % % % 0,34 0,00 0,00 0,00 0,29 0,34 0,00 3,39 1,76 2,34 2,78 2,29 3,03 1,88 4,13 3,03 2,91 3,88 4,74 4,17 3,52 7,89 7,64 8,06 7,89 8,06 8,50 7,45 7,57 7,10 8,01 7,57 7,69 8,18 7,28 6,05 5,81 6,30 5,88 6,67 6,67 6,13 3,52 3,91 3,86 3,81 3,13 3,86 3,39 Can anyone help me with this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a column based on a index
Dim cell as Range, rng as Range
for each cell in Range(somthing) if cell.Interior.colorindex = 3 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if Next if not rng is nothing then rng.copy destination:=activesheet.Next.Range("A1") End if -- Regards, Tom Ogilvy "VH" wrote in message ... I want to be able to select several columns based on a number in row 6. These selections will be copied out to another sheet to get the average value and based on these datas make a chart. I know how to select the index numbers and based on their value change their color index. The next step is to, based on these color indexes, select the range with all the numbers in their column. 1 2 1 2 3 1 2 % % % % % % % 0,34 0,00 0,00 0,00 0,29 0,34 0,00 3,39 1,76 2,34 2,78 2,29 3,03 1,88 4,13 3,03 2,91 3,88 4,74 4,17 3,52 7,89 7,64 8,06 7,89 8,06 8,50 7,45 7,57 7,10 8,01 7,57 7,69 8,18 7,28 6,05 5,81 6,30 5,88 6,67 6,67 6,13 3,52 3,91 3,86 3,81 3,13 3,86 3,39 Can anyone help me with this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a column based on a index
Thank you Tom for your fast reply!
Only one problem, I get an error: Next without For. Something missing? Tom Ogilvy skrev: Dim cell as Range, rng as Range for each cell in Range(somthing) if cell.Interior.colorindex = 3 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if Next if not rng is nothing then rng.copy destination:=activesheet.Next.Range("A1") End if -- Regards, Tom Ogilvy "VH" wrote in message ... I want to be able to select several columns based on a number in row 6. These selections will be copied out to another sheet to get the average value and based on these datas make a chart. I know how to select the index numbers and based on their value change their color index. The next step is to, based on these color indexes, select the range with all the numbers in their column. 1 2 1 2 3 1 2 % % % % % % % 0,34 0,00 0,00 0,00 0,29 0,34 0,00 3,39 1,76 2,34 2,78 2,29 3,03 1,88 4,13 3,03 2,91 3,88 4,74 4,17 3,52 7,89 7,64 8,06 7,89 8,06 8,50 7,45 7,57 7,10 8,01 7,57 7,69 8,18 7,28 6,05 5,81 6,30 5,88 6,67 6,67 6,13 3,52 3,91 3,86 3,81 3,13 3,86 3,39 Can anyone help me with this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a column based on a index
Added an End If and it worked in one way. I got a copy of the index numbers,
but not the rest of the column belonging to the index. VH skrev: Thank you Tom for your fast reply! Only one problem, I get an error: Next without For. Something missing? Tom Ogilvy skrev: Dim cell as Range, rng as Range for each cell in Range(somthing) if cell.Interior.colorindex = 3 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if Next if not rng is nothing then rng.copy destination:=activesheet.Next.Range("A1") End if -- Regards, Tom Ogilvy "VH" wrote in message ... I want to be able to select several columns based on a number in row 6. These selections will be copied out to another sheet to get the average value and based on these datas make a chart. I know how to select the index numbers and based on their value change their color index. The next step is to, based on these color indexes, select the range with all the numbers in their column. 1 2 1 2 3 1 2 % % % % % % % 0,34 0,00 0,00 0,00 0,29 0,34 0,00 3,39 1,76 2,34 2,78 2,29 3,03 1,88 4,13 3,03 2,91 3,88 4,74 4,17 3,52 7,89 7,64 8,06 7,89 8,06 8,50 7,45 7,57 7,10 8,01 7,57 7,69 8,18 7,28 6,05 5,81 6,30 5,88 6,67 6,67 6,13 3,52 3,91 3,86 3,81 3,13 3,86 3,39 Can anyone help me with this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a column based on a index
Dim cell as Range, rng as Range
for each cell in Range(somthing) if cell.Interior.colorindex = 3 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if end if Next if not rng is nothing then rng.entireColumn.copy destination:=activesheet.Next.Range("A1") End if -- Regards, Tom Ogilvy "VH" wrote in message ... Added an End If and it worked in one way. I got a copy of the index numbers, but not the rest of the column belonging to the index. VH skrev: Thank you Tom for your fast reply! Only one problem, I get an error: Next without For. Something missing? Tom Ogilvy skrev: Dim cell as Range, rng as Range for each cell in Range(somthing) if cell.Interior.colorindex = 3 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if Next if not rng is nothing then rng.copy destination:=activesheet.Next.Range("A1") End if -- Regards, Tom Ogilvy "VH" wrote in message ... I want to be able to select several columns based on a number in row 6. These selections will be copied out to another sheet to get the average value and based on these datas make a chart. I know how to select the index numbers and based on their value change their color index. The next step is to, based on these color indexes, select the range with all the numbers in their column. 1 2 1 2 3 1 2 % % % % % % % 0,34 0,00 0,00 0,00 0,29 0,34 0,00 3,39 1,76 2,34 2,78 2,29 3,03 1,88 4,13 3,03 2,91 3,88 4,74 4,17 3,52 7,89 7,64 8,06 7,89 8,06 8,50 7,45 7,57 7,10 8,01 7,57 7,69 8,18 7,28 6,05 5,81 6,30 5,88 6,67 6,67 6,13 3,52 3,91 3,86 3,81 3,13 3,86 3,39 Can anyone help me with this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a column based on a index
Thanks! Works great!
Tom Ogilvy skrev: Dim cell as Range, rng as Range for each cell in Range(somthing) if cell.Interior.colorindex = 3 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if end if Next if not rng is nothing then rng.entireColumn.copy destination:=activesheet.Next.Range("A1") End if -- Regards, Tom Ogilvy "VH" wrote in message ... Added an End If and it worked in one way. I got a copy of the index numbers, but not the rest of the column belonging to the index. VH skrev: Thank you Tom for your fast reply! Only one problem, I get an error: Next without For. Something missing? Tom Ogilvy skrev: Dim cell as Range, rng as Range for each cell in Range(somthing) if cell.Interior.colorindex = 3 then if rng is nothing then set rng = cell else set rng = union(rng,cell) end if Next if not rng is nothing then rng.copy destination:=activesheet.Next.Range("A1") End if -- Regards, Tom Ogilvy "VH" wrote in message ... I want to be able to select several columns based on a number in row 6. These selections will be copied out to another sheet to get the average value and based on these datas make a chart. I know how to select the index numbers and based on their value change their color index. The next step is to, based on these color indexes, select the range with all the numbers in their column. 1 2 1 2 3 1 2 % % % % % % % 0,34 0,00 0,00 0,00 0,29 0,34 0,00 3,39 1,76 2,34 2,78 2,29 3,03 1,88 4,13 3,03 2,91 3,88 4,74 4,17 3,52 7,89 7,64 8,06 7,89 8,06 8,50 7,45 7,57 7,10 8,01 7,57 7,69 8,18 7,28 6,05 5,81 6,30 5,88 6,67 6,67 6,13 3,52 3,91 3,86 3,81 3,13 3,86 3,39 Can anyone help me with this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
Need to select rows to average based on a value in a different column | New Users to Excel | |||
Select a column based on a cell entry | Excel Programming | |||
How to select columns using column index | Excel Programming | |||
how would i select a group of rows based on a column value | Excel Programming |