Dynamic selection of a range
 

I have a VBA-program that I wants to loop through several collumns of data
and plot this data in different graphs.

When I record a macro, where I select the three collums of interest and
generate a graph, the result looks like this.

Range("A:A,D:D,K:K").Select
Range("K1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("PEc_measdata20070907_0833").Range( _
"A1:A3501,D1:D3501,K1:K3501"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
"PEc_measdata20070907_0833"

How can I change the code so that I in the firs turn plots column A, B and
I. The next turn A, C and J, then A, D and K, then A, E and L, then A, F and
M, and finaly A, G and N.

At the same time the lengt of data can vary. Therfore I have a variable
called No_of_Data. In the example above this variable shuld be 3501. How can
I include this in my program.

BR,
/Sören

Dynamic selection of a range
 

For i = 1 To 6

sSourceData = Range(Cells(1, 1), Cells(3501, 1)).Address
sSourceData = sSourceData & "," & Range(Cells(1, i + 1), Cells(3501,
i + 1)).Address
sSourceData = sSourceData & "," & Range(Cells(1, i + 8), Cells(3501,
i + 8)).Address
Charts.Add
With ActiveChart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData _
Source:=Sheets("PEc_measdata20070907_0833").Range( _
sSourceData), _
PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:= _
"PEc_measdata20070907_0833"
End With
Next i


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sören_Marodören" wrote in message
...
I have a VBA-program that I wants to loop through several collumns of data
and plot this data in different graphs.

When I record a macro, where I select the three collums of interest and
generate a graph, the result looks like this.

Range("A:A,D:D,K:K").Select
Range("K1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("PEc_measdata20070907_0833").Range( _
"A1:A3501,D1:D3501,K1:K3501"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
"PEc_measdata20070907_0833"

How can I change the code so that I in the firs turn plots column A, B and
I. The next turn A, C and J, then A, D and K, then A, E and L, then A, F
and
M, and finaly A, G and N.

At the same time the lengt of data can vary. Therfore I have a variable
called No_of_Data. In the example above this variable shuld be 3501. How
can
I include this in my program.

BR,
/Sören

Dynamic selection of a range
 

Thanks Bob!

This was a good help for me.
I have not been using .Address before. Have to study this featur more.

Best regards,
/Sören

"Bob Phillips" wrote:

For i = 1 To 6

sSourceData = Range(Cells(1, 1), Cells(3501, 1)).Address
sSourceData = sSourceData & "," & Range(Cells(1, i + 1), Cells(3501,
i + 1)).Address
sSourceData = sSourceData & "," & Range(Cells(1, i + 8), Cells(3501,
i + 8)).Address
Charts.Add
With ActiveChart
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData _
Source:=Sheets("PEc_measdata20070907_0833").Range( _
sSourceData), _
PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:= _
"PEc_measdata20070907_0833"
End With
Next i


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sören_Marodören" wrote in message
...
I have a VBA-program that I wants to loop through several collumns of data
and plot this data in different graphs.

When I record a macro, where I select the three collums of interest and
generate a graph, the result looks like this.

Range("A:A,D:D,K:K").Select
Range("K1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("PEc_measdata20070907_0833").Range( _
"A1:A3501,D1:D3501,K1:K3501"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:= _
"PEc_measdata20070907_0833"

How can I change the code so that I in the firs turn plots column A, B and
I. The next turn A, C and J, then A, D and K, then A, E and L, then A, F
and
M, and finaly A, G and N.

At the same time the lengt of data can vary. Therfore I have a variable
called No_of_Data. In the example above this variable shuld be 3501. How
can
I include this in my program.

BR,
/Sören