Automatize chart building

[tuli]

I am trying to automatize the construction of a chart. It has 50
series.
I recorded a macro and I am trying to modify it to work in a loop.
I was thinking to use the cells functions so I can use indexes.
Could not find a way to define ranges with the cells function.
Any ideas?

Thanks

Here is the macro:

Sub Macro1()
Sheets("Chart1").Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(20).XValues = "=Sheet1!R2C1:R61C1"
ActiveChart.SeriesCollection(20).Values = "=Sheet1!R2C25:R61C25"
ActiveChart.SeriesCollection(20).Name = "=Sheet1!R1C25"
End Sub

Here is what I would like to have

Sub Macro1()
Sheets("Chart1").Select

for i=1 to 50

Application.CutCopyMode = False
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(20).XValues =
range(cells(2,1),cells(62,1))
ActiveChart.SeriesCollection(20).Values = range(cells(2,i
+1),cells(62,i+1))
ActiveChart.SeriesCollection(20).Name = range(cells(1,i+1))
next i
End Sub

 

[Bob Phillips]

Untested

With ActiveChart

Application.CutCopyMode = False

For i = 1 To 50

.SeriesCollection.NewSeries
With .SeriesCollection(i)

.XValues = "=" & Range(Cells(2, 1), Cells(62, 1)).Address(, , ,
True)
.Values = "=" & Range(Cells(2, i + 1), Cells(62, i +
1)).Address(, , , True)
.Name = "=" & Range(Cells(1, i + 1)).Address(, , , True)
End With
Next i
End With