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.
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.
Code:
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