Excel: Dynamically Update Chart Series in for loop

Question

I have created a macro which will be used to create an individual chart for each of the over 2000 items who's data is stored in another Excel workbook. The macro goes through a for loop, creating a new chart, setting the chart series using the data from the other Excel workbook and then doing all of the formatting work after that. I am however still having a problem trying to dynamically update the series range after each for loop. A sample of the series is found below:

for i = 1 to Row.Count
    ActiveChart.FullSeriesCollection(1).Values = _
    "='[Simplified Interactive - V2.xlsm]Maint. FDC'!$D$2:$BA$2"

I was wondering how I would be able to modify the above static range, and make it dynamic so that the second chart uses data from row 3, the third chart uses data from row 4 and so on through the entire range.

Entire Code For Reference:

Sub Macro4()


Dim ws, ws2 As Worksheet
Dim graphName As String
Dim i As Integer
Dim srange As Range
Dim grp As Chart
Dim lw As Long


Set ws = Sheets("Interactive Data")
Set ws2 = Sheets("Graphs")


    For i = 1 To 3 'Row.Count

    ws2.Shapes.AddChart2(227, xlLine).Select
    With ActiveChart
        .Parent.Name = ws.Cells(i + 1, 1)
    End With
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = "=""Annual Inv."""
    ActiveChart.FullSeriesCollection(1).Values = _
        "='[Simplified Interactive - V2.xlsm]Maint. FDC'!$D$" & i + 1 & ":$BA$" & i + 1 '*****ADJUST THIS
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=""Status Quo"""
    ActiveChart.FullSeriesCollection(2).Values = _
        "='[Simplified Interactive - V2.xlsm]No Maint. FDC'!$D$" & i + 1 & ":$BA$" & i + 1 '*****ADJUST THIS
    ActiveChart.FullSeriesCollection(2).XValues = "=Graphs!$A$1:$AW$1"
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).ReversePlotOrder = True
    ActiveChart.Axes(xlValue).Crosses = xlMaximum
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).TickLabels.Font.Color = RGB(0, 0, 0)
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).TickLabels.Font.Color = RGB(0, 0, 0)
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.SetElement (msoElementLegendRight)
    ActiveChart.Legend.Select
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    ActiveChart.Legend.LegendEntries(2).Select
    ActiveChart.Legend.LegendEntries(1).Select
    ActiveChart.Legend.Select
    With Selection.Format.TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.ChartTitle.Select
    Selection.Characters.Text = "Degradation"
    With Selection.Format.TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    With Selection.Format.TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Year"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Year"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.SetElement msoElementPrimaryValueAxisTitleBelowAxis
    ActiveChart.Axes(xlValue).AxisTitle.Select
    With Selection.Format.TextFrame2.TextRange.Characters(1, 10).Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
        .Solid
    End With
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Condition"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Condition"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 30).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 8).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(9, 22).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(192, 0, 0)
        .Transparency = 0
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 112, 192)
        .Transparency = 0
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 53.6250393701, _
        113.1250393701, 76.5, 15.75).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Status Quo"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 28).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 28).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Size = 8
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange.ScaleWidth 1.568627451, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0476190476, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.IncrementLeft -6
    Selection.ShapeRange.IncrementTop 6
    With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 0, 0)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.PlotArea.Select
    ActiveChart.Shapes.Range(Array("TextBox 1")).Select
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 58.8750393701, _
        42.3750393701, 67.5, 12.75).Select
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.PlotArea.Select
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 67.8750393701, _
        45.6250393701, 104.25, 11.25).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Optimal"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 17).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 7).Font
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Size = 8
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(8, 10).Font
        .BaselineOffset = 0
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Size = 8
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange.ScaleHeight 1.4666666667, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 0.7553956835, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.0454545455, msoFalse, _
        msoScaleFromBottomRight
    Selection.ShapeRange.IncrementLeft -22.5
    Selection.ShapeRange.IncrementTop 12.75
    Range("R16").Select


    Next i

End Sub

Answer 1

I was wondering how I would be able to modify the above static range, and make it dynamic so that the second chart uses data from row 3, the third chart uses data from row 4 and so on through the entire range.

By using a variable?

rw = 2

ActiveChart.FullSeriesCollection(1).Values = _
"='[Simplified Interactive - V2.xlsm]Maint. FDC'!$D$" & rw & ":$BA$" & rw

And if you want it "connected" to your For Loop i.e instead of using rw variable, you want to use i then

ActiveChart.FullSeriesCollection(1).Values = _
"='[Simplified Interactive - V2.xlsm]Maint. FDC'!$D$" & i & ":$BA$" & i

EDIT

the graph only plots the points for the first chart, but doesn't plot the series for charts 2 and beyond. The data does exist, so Any idea why it would be doing that? – Xcelrate

The code plots for all the 3 graphs for me. BTW, avoid the use of Activechart. Work with Objects. Here is a very basic example of how your code will look like.

Sub Sample()
    Dim ws As Worksheet
    Dim objChrt As ChartObject
    Dim myChart As Chart
    Dim chartTop As Long

    Set ws = Sheets("Graphs")

    '~~> This will define the "Left" of the chart
    chartleft = 10

    For i = 1 To 3
        Set objChrt = ws.ChartObjects.Add(chartleft, 10, 200, 200)
        Set myChart = objChrt.Chart

        With myChart
            .SeriesCollection.NewSeries
            .FullSeriesCollection(1).Name = "Test"
            .FullSeriesCollection(1).Values = "='Maint. FDC'!$D$" & i + 1 & ":$BA$" & i + 1
        End With

        chartleft = chartleft + 220
    Next i
End Sub

Worksheet Maint. FDC

Worksheet Graphs

Interesting Read

ChartObjects.Add Method