Coding the series collections of a chart

Giganews Newsgroups
Subject: Coding the series collections of a chart
Posted by:  troy_l…@comcast.net
Date: Wed, 25 Mar 2009

I need some help with the code to assign values to a series collection
for charts. This is the first chart I have tried to do with VBA.

My first question is how do I properly set the SetSourceData Source?
This line is commented out below. All of the range arguments on this
line are verified to be correct (I created them in another piece of
code not shown here).

How do I treat the X axis? This should be SeriesCollection(1), right?

Also, am I assigning the range correctly for the values statements for
the series collections?

Finally, I want the 4th series collection (EOH) to be on a secondary Y
axis. The last line of code is what the macro recorder came up with
for this. Is this correct?

Any ideas are welcomed, including a more efficient way to do this.
Thanks for the help in advance.

'Create the Year to Year Summary Chart.
    Sheets("Scorecard").Activate

    Set chtYTY = ActiveSheet.ChartObjects.Add(Left:=250, Width:=375,
Top:=75, Height:=225)

    With chtYTY.Chart
        .ChartType = xlLineMarkers
        '.SetSourceData Source:=Sheets("Data").Range(rngXvalues,
rngShipped, rngIn, rngEOH),
        'PlotBy:=xlColumns
    End With

    'Clear out all Series that Excel may have created by default.
    With chtYTY.Chart
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop

    'Set up the X axis values
        With .SeriesCollection.NewSeries
            .XValues = "rngXvalues"
            .Name = "Week Number"
        End With

    'First Y axis series.
        With .SeriesCollection.NewSeries
            .Name = "=Shipped"
            .Values = "=Data!Range(rngShipped)"
            .XValues = "=Data!rngXvalues"
        End With

    'Second Y axis series.
      With .SeriesCollection.NewSeries
            .Name = "In"
            .Values = "rngIn"
            .XValues = "rngXvalues"
        End With

    'Third Y axis series.
        With .SeriesCollection.NewSeries
            .Name = "EOH"
            .Values = "rngEOH"
            .XValues = "rngXvalues"
        End With

        '.Location Where:=xlLocationAsObject, Name:="Scorecard"
        .HasTitle = True
        .ChartTitle.Characters.Text = "Year to Year Ships"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week
Number"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Number
of Units"
        .HasDataTable = False

    'This is code for the secondary axis
        .SeriesCollection(4).AxisGroup = 2

    End With

Replies