Charting in a loop
 

Excel XP, Win XP
I have 14 rows of data I want to chart, each row in a different chart.
The following code charts row 1 as the "X" axis and the values in row 2 as
the "Y" axis. Good.
Sub TestChart()
Range("C1:R2").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("By store").Range("C1:R2")
ActiveChart.Location Whe=xlLocationAsNewSheet
End Sub
This code creates only one chart, as expected.
But I want to create charts of row 1 versus each of the other rows (13
charts).
I've tried a loop through all the rows using a Union of row 1 and each other
row as the range. Didn't work.
I know I can copy row 1 and each of the other rows, in turn, to a Utility
sheet, and chart that.
My question: How can I create the 13 charts without resorting to the Utility
sheet method?
Thanks for your time. Otto

Charting in a loop
 

Union worked just fine in this simple procedu

Sub ChartEachRow()
Dim iRow As Long
Dim cht As Chart
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
For iRow = 2 To 14
Set rng = Union(wks.Range("C1:R1"), wks.Range("C" & iRow & ":R" & iRow))
Set cht = ActiveWorkbook.Charts.Add
cht.SetSourceData Source:=rng
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Otto Moehrbach" wrote in message
...
Excel XP, Win XP
I have 14 rows of data I want to chart, each row in a different chart.
The following code charts row 1 as the "X" axis and the values in row 2 as
the "Y" axis. Good.
Sub TestChart()
Range("C1:R2").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("By store").Range("C1:R2")
ActiveChart.Location Whe=xlLocationAsNewSheet
End Sub
This code creates only one chart, as expected.
But I want to create charts of row 1 versus each of the other rows (13
charts).
I've tried a loop through all the rows using a Union of row 1 and each
other row as the range. Didn't work.
I know I can copy row 1 and each of the other rows, in turn, to a Utility
sheet, and chart that.
My question: How can I create the 13 charts without resorting to the
Utility sheet method?
Thanks for your time. Otto

Charting in a loop
 

Jon
Thanks for that. I'll try it and see. Otto
"Jon Peltier" wrote in message
...
Union worked just fine in this simple procedu

Sub ChartEachRow()
Dim iRow As Long
Dim cht As Chart
Dim rng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
For iRow = 2 To 14
Set rng = Union(wks.Range("C1:R1"), wks.Range("C" & iRow & ":R" &
iRow))
Set cht = ActiveWorkbook.Charts.Add
cht.SetSourceData Source:=rng
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Otto Moehrbach" wrote in message
...
Excel XP, Win XP
I have 14 rows of data I want to chart, each row in a different chart.
The following code charts row 1 as the "X" axis and the values in row 2
as the "Y" axis. Good.
Sub TestChart()
Range("C1:R2").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("By store").Range("C1:R2")
ActiveChart.Location Whe=xlLocationAsNewSheet
End Sub
This code creates only one chart, as expected.
But I want to create charts of row 1 versus each of the other rows (13
charts).
I've tried a loop through all the rows using a Union of row 1 and each
other row as the range. Didn't work.
I know I can copy row 1 and each of the other rows, in turn, to a Utility
sheet, and chart that.
My question: How can I create the 13 charts without resorting to the
Utility sheet method?
Thanks for your time. Otto