Charting: Add Series based on If Statement

[alexbell12]

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

 

[Hui]

Alexbell12


Try changing this towards the end of the Subroutine:

[pre]

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

[/pre]

 

[alexbell12]

Hi Hui,


Thx for the suggestion. It still says "Compile Error: Object Required" though...

 

[Hui]

Try the following

there are a few changes:

[pre]

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 = Wks.ChartObjects("Chart 1")

ActiveSheet.ChartObjects("Chart 1").Activate

SeriesNumber = ActiveChart.SeriesCollection.Count

Range("AE7").FormulaR1C1 = SeriesNumber

If Range("AE7") = Range("AE6") Then Exit Sub
If Range("AE7") < Range("AE6") Then
XAvis = "=RusOilGas!Duration10"
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

[/pre]

 

[alexbell12]

I got a "Run-time error 438: Object doesn't support this property or method". Strange because it looks like it should work...

 

[NARAYANK991]

Hi Alex ,


Make the following 2 changes :


1. Set NewSeries = ActiveChart.SeriesCollection.NewSeries


instead of


Set NewSeries = Chart1.SeriesCollection.NewSeries


2. XAxis = "=RusOilGas!Duration10"


instead of


XAvis = "=RusOilGas!Duration10"


Narayan

 

[alexbell12]

Excellent! Thanks very much both of you.