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("D55"). 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 ("D55") (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