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

Siddharth Rout · Accepted Answer · 2017-06-26 13:53:57Z

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.

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

edited Jun 26, 2017 at 13:53

answered Jun 26, 2017 at 12:38

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Xcelrate Over a year ago

So I changed it to "='[Simplified Interactive - V2.xlsm]Maint. FDC'!$D$" & i + 1 & ":$BA$" & i + 1 but now 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?

Siddharth Rout Over a year ago

Because you are mentioning ActiveChart. Which ever chart is active, it will change it for that. In your code are you looping through the charts as well?

Xcelrate Over a year ago

I create a new chart at the start of every loop using ws2.Shapes.AddChart2(227, xlLine).Select, where ws2 is the worksheet where I am creating the graphs. So I would assume that the new chart is the active one as I iterate through the range?

Siddharth Rout Over a year ago

Can you update your question with the exact code that you are using?

Xcelrate Over a year ago

Updated, the top portion of the code is where the series is added. Much of the remainder of the code is just chart formatting such as axis, colours etc. so you don't need to really focus on the bottom half.

|

Collectives™ on Stack Overflow

Excel: Dynamically Update Chart Series in for loop

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related