Vfrdtyky

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

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