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?

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?

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?

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?

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?

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?