title: Excel: Dynamically Update Chart Series in for loop
body: <p>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:  </p>
  
  <pre><code>for i = 1 to Row.Count
      ActiveChart.FullSeriesCollection(1).Values = _
      "='[Simplified Interactive - V2.xlsm]Maint. FDC'!$D$2:$BA$2"
  </code></pre>
  
  <p>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.</p>
  
  <p>Entire Code For Reference: </p>
  
  <pre><code>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$" &amp; i + 1 &amp; ":$BA$" &amp; 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$" &amp; i + 1 &amp; ":$BA$" &amp; 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
  </code></pre>