Hi All,

I am trying to create a macro that will conditionally add a new series to a chart based on if the number of existing chart series is less than a COUNT formula in the excel sheet.

Here is what I have written so far, but it is not working, and i'm getting a compile error. Can anyone suggest some alterations? Cell AE6 is the result of formula to count the number of values in the sheet.


Sub EditGraphs()

Dim Wks As Worksheet
Dim Chart1 As ChartObject
Dim NewSeries As Series
Dim XAxis As String
Dim YAxis As String


Set Wks = ActiveSheet
Set Chart1 = ActiveSheet.ChartObjects("Chart 1")

ActiveSheet.ChartObjects("Chart 1").Activate

SeriesNumber = ActiveChart.SeriesCollection.Count

Range("AE7").Select
ActiveCell.FormulaR1C1 = SeriesNumber

If Range("AE7") = Range("AE6") Then Exit Sub

If Range("AE7") < Range("AE6") Then

Set XAvis = "RusOilGas!Duration10"
Set YAxis = "RusOilGas!Spread10"

Set NewSeries = Chart1.SeriesCollection.NewSeries

With NewSeries
Name = Range("BE13").Offset(1, 0)
XValues = XAxis
Values = YAxis

End With

End If

End Sub