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
excel vba excel-vba
add a comment |
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
excel vba excel-vba
add a comment |
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
excel vba excel-vba
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
excel vba excel-vba
edited Jun 26 '17 at 13:14
asked Jun 26 '17 at 12:36
Xcelrate
318
318
add a comment |
add a comment |
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
Worksheet Graphs
Interesting Read
ChartObjects.Add Method
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 mentioningActiveChart
. 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 usingws2.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
|
show 4 more comments
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
Worksheet Graphs
Interesting Read
ChartObjects.Add Method
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 mentioningActiveChart
. 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 usingws2.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
|
show 4 more comments
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
Worksheet Graphs
Interesting Read
ChartObjects.Add Method
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 mentioningActiveChart
. 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 usingws2.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
|
show 4 more comments
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
Worksheet Graphs
Interesting Read
ChartObjects.Add Method
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 '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 mentioningActiveChart
. 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 usingws2.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
|
show 4 more comments
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 mentioningActiveChart
. 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 usingws2.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
|
show 4 more comments
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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