Nick -
This isn't exactly what you're going to need, because it just uses the
column A data for the X values, and B for the Y, plus the first cell in
column C in that block of data for the series name. Your code uses
columns 6, 15, and 7 for this. But it steps down to each block, and
uses just that block for the next chart.
Sub MakeCharts()
Dim myCell As Range
Dim myCell2 As Range
Dim myRange As Range
Dim myChartObject As ChartObject
Dim mySeries As Series
Set myCell = ActiveSheet.Cells(1, 1)
Debug.Print myCell.Address
If Len(myCell.Text) = 0 Then
Set myCell = myCell.End(xlDown)
Debug.Print myCell.Address
End If
Do
Set myCell2 = myCell.End(xlDown)
Set myRange = ActiveSheet.Range(myCell, myCell2)
Set myChartObject = ActiveSheet.ChartObjects.Add _
(myCell.Offset(0, 3).Left, myCell.Top, 350, 275)
Set mySeries = myChartObject.Chart.SeriesCollection.NewSeries
With mySeries
.Values = myRange.Offset(0, 1)
.XValues = myRange
.Name = "=" & myRange.Offset(0, 2).Resize(1, 1).Address _
(ReferenceStyle:=xlR1C1, external:=True)
End With
Set myCell = myCell2.End(xlDown)
Debug.Print myCell.Address
Loop Until myCell.Row = 65536
End Sub
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
Nick wrote:
I'm stuck with the next problem. I have two rows of data that look
like this:
0 97.7 1
20 95.6 1
40 89.6 1
60 89.0 1
0 90.4 2
20 90.7 2
40 90.9 2
60 89.9 2
80 88.4 2
0 89.8 3
.... .... and this goes on to 160
What I want to make charts with this data. Like on the x-axis the data
in the left columnt and on Y-axis data from the right column. So this
goes pretty easy with this:
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection(1).XValues = "='TEMPLATE'!R6C6:R17C6"
ActiveChart.SeriesCollection(1).Values =
"='TEMPLATE'!R6C15:R17C15"
ActiveChart.SeriesCollection(1).name = "='TEMPLATE'!R6C7"
etc...
Now, is there a way to make this go automatically? So that the macro
finds the first empty cell in the row, makes a chart with the data,
gives the chart a name, and then goes on to the next set of data and
makes a new chart...?