Excel: Dynamically Update Chart Series in for loop











up vote
2
down vote

favorite












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









share|improve this question




























    up vote
    2
    down vote

    favorite












    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









    share|improve this question


























      up vote
      2
      down vote

      favorite









      up vote
      2
      down vote

      favorite











      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









      share|improve this 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






      excel vba excel-vba






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jun 26 '17 at 13:14

























      asked Jun 26 '17 at 12:36









      Xcelrate

      318




      318
























          1 Answer
          1






          active

          oldest

          votes

















          up vote
          1
          down vote



          accepted











          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



          enter image description here



          Worksheet Graphs



          enter image description here



          Interesting Read



          ChartObjects.Add Method






          share|improve this answer























          • 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?
            – Xcelrate
            Jun 26 '17 at 13:00










          • 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?
            – Siddharth Rout
            Jun 26 '17 at 13:02










          • 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?
            – Xcelrate
            Jun 26 '17 at 13:06










          • Can you update your question with the exact code that you are using?
            – Siddharth Rout
            Jun 26 '17 at 13:07










          • 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.
            – Xcelrate
            Jun 26 '17 at 13:15













          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














           

          draft saved


          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f44759893%2fexcel-dynamically-update-chart-series-in-for-loop%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          1
          down vote



          accepted











          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



          enter image description here



          Worksheet Graphs



          enter image description here



          Interesting Read



          ChartObjects.Add Method






          share|improve this answer























          • 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?
            – Xcelrate
            Jun 26 '17 at 13:00










          • 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?
            – Siddharth Rout
            Jun 26 '17 at 13:02










          • 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?
            – Xcelrate
            Jun 26 '17 at 13:06










          • Can you update your question with the exact code that you are using?
            – Siddharth Rout
            Jun 26 '17 at 13:07










          • 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.
            – Xcelrate
            Jun 26 '17 at 13:15

















          up vote
          1
          down vote



          accepted











          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



          enter image description here



          Worksheet Graphs



          enter image description here



          Interesting Read



          ChartObjects.Add Method






          share|improve this answer























          • 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?
            – Xcelrate
            Jun 26 '17 at 13:00










          • 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?
            – Siddharth Rout
            Jun 26 '17 at 13:02










          • 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?
            – Xcelrate
            Jun 26 '17 at 13:06










          • Can you update your question with the exact code that you are using?
            – Siddharth Rout
            Jun 26 '17 at 13:07










          • 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.
            – Xcelrate
            Jun 26 '17 at 13:15















          up vote
          1
          down vote



          accepted







          up vote
          1
          down vote



          accepted







          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



          enter image description here



          Worksheet Graphs



          enter image description here



          Interesting Read



          ChartObjects.Add Method






          share|improve this answer















          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



          enter image description here



          Worksheet Graphs



          enter image description here



          Interesting Read



          ChartObjects.Add Method







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jun 26 '17 at 13:53

























          answered Jun 26 '17 at 12:38









          Siddharth Rout

          113k14148200




          113k14148200












          • 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?
            – Xcelrate
            Jun 26 '17 at 13:00










          • 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?
            – Siddharth Rout
            Jun 26 '17 at 13:02










          • 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?
            – Xcelrate
            Jun 26 '17 at 13:06










          • Can you update your question with the exact code that you are using?
            – Siddharth Rout
            Jun 26 '17 at 13:07










          • 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.
            – Xcelrate
            Jun 26 '17 at 13:15




















          • 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?
            – Xcelrate
            Jun 26 '17 at 13:00










          • 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?
            – Siddharth Rout
            Jun 26 '17 at 13:02










          • 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?
            – Xcelrate
            Jun 26 '17 at 13:06










          • Can you update your question with the exact code that you are using?
            – Siddharth Rout
            Jun 26 '17 at 13:07










          • 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.
            – Xcelrate
            Jun 26 '17 at 13:15


















          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?
          – Xcelrate
          Jun 26 '17 at 13:00




          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?
          – Xcelrate
          Jun 26 '17 at 13:00












          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?
          – Siddharth Rout
          Jun 26 '17 at 13:02




          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?
          – Siddharth Rout
          Jun 26 '17 at 13:02












          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?
          – Xcelrate
          Jun 26 '17 at 13:06




          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?
          – Xcelrate
          Jun 26 '17 at 13:06












          Can you update your question with the exact code that you are using?
          – Siddharth Rout
          Jun 26 '17 at 13:07




          Can you update your question with the exact code that you are using?
          – Siddharth Rout
          Jun 26 '17 at 13:07












          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.
          – Xcelrate
          Jun 26 '17 at 13:15






          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.
          – Xcelrate
          Jun 26 '17 at 13:15




















           

          draft saved


          draft discarded



















































           


          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f44759893%2fexcel-dynamically-update-chart-series-in-for-loop%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Bressuire

          Vorschmack

          Quarantine