Adding New Chart Series with Loop

[aerodoc]

So, this works to add a series


Sub AddNewSeries()

With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("B5")
.Values = ActiveSheet.Range("D5:D5")
.XValues = ActiveSheet.Range("C5:C5")
End With

End Sub

But I really need to reference the ranges as "Cells" so that I can put the values in a loop

For example, I want Range(Cells(4,5) , Cells(4,5)) instead of Range("D5:D5"). But when I do this, I get an error.

Any idea how to make this work?

[snb]

with the use of offset you can use any loop:


Sub AddNewSeries()
for j=0 to 5
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("B5").offset(j)
.Values = ActiveSheet.Range("D5").offset(j)
.XValues = ActiveSheet.Range("C5").offset(j)
End With
next

End Sub

I hope you know that ActiveSheet.Range("D5:D5") isn't a conventional way of writing VBA code.

[aerodoc]

Thanks, that works for my situation.

Perhaps you can clarify what you mean? I was trying to make it more general since ("D5:D5") (as stated) could really be ("D5:G5"). If I was always just using a single column then I wouldn't have written it that way. I figured I would present a more general solution request (though I didn't present it correctly). It works the same if spanning multiple columns though so all is good.

[snb]

without offset


Sub AddNewSeries()
For j=0 To 5
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.cells(5+j,2) ' B5
.Values = ActiveSheet.cells(5+j,4).resize(,10) ' D5:O5
.XValues = ActiveSheet.cells(5+j,3).resize(,4) ' C5:I5
End With
Next
End Sub