Adding New Chart Series with Loop

So, this works to add a series

Code:

---

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?

with the use of offset you can use any loop:

Code:

---

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.

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.

without offset

Code:

---

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

---