CODEWITHSUNDEEP
excelvbapivot-tablepivot-chart
Janice Luong
Janice Luong

Reputation: 27

Creating a permanente trendline in Pivot Chart for a single chart value

I am trying to create a permanente trendline for only one of the chart values on a pivot chart. I've written a macro for this, but it seems like my if-else statement in my for loop is being ignored.

Here is my code in the Moduel1:

Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection

Set mySeriesCol = ActiveSheet.ChartObjects.Chart.SeriesCollection

For i = 1 To mySeriesCol.Count
    If mySeriesCol(i).Name <> "Actual" & mySeriesCol(i).Trendlines.Count > 0 Then
        mySeriesCol(i).Trendlines.Delete
    ElseIf mySeriesCol(i).Name = "Actual" & mySeriesCol(i).Trendlines.Count = 0 Then
        mySeriesCol(i).Trendlines.Add
End If
Next
End Sub

And here is my code in Sheet2:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine
End Sub

Here is what I am getting:

Here is what I am getting

Here is what I want to see:

Here is what I want to see

Any advice would be greatly appreciated.

Upvotes: 0

Views: 399

Answers (2)

Janice Luong
Janice Luong

Reputation: 27

I was able to figure it out. Here is my final solution:

Module:

Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection

Set mySeriesCol = Sheet2.ChartObjects(1).Chart.SeriesCollection

For i = 1 To mySeriesCol.Count
    If mySeriesCol(i).Name <> "Actual" And mySeriesCol(i).Trendlines.Count > 0 Then
    mySeriesCol(i).Trendlines(1).Delete
ElseIf mySeriesCol(i).Name = "Actual" And mySeriesCol(i).Trendlines.Count = 0 Then
    mySeriesCol(i).Trendlines.Add
ElseIf mySeriesCol(i).Name = "Actual" And mySeriesCol(i).Trendlines.Count > 1 Then
    mySeriesCol(i).Trendlines(1).Delete
End If
Next
End Sub

Sheet2: 

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine
End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 50042

& forces string concatenation and is not a logical operator; And is.

Currently mySeriesCol(i).Trendlines.Count > 0 is evaluated, and the result (True/False) is being concatenated with & to the text "Actual".

So your current code is equivalent to

If mySeriesCol(i).Name <> "ActualFalse" '<~ or "ActualTrue"

Use And.

EDIT:

Also problematic: Set mySeriesCol = ActiveSheet.ChartObjects.Chart.SeriesCollection.

You want to work with a specific ChartObject.

Sheet1.ChartObjects("your chart name").Chart.SeriesCollection

or

Sheet1.ChartObjects(1).Chart.SeriesCollection

EDIT 2:

There's no Trendlines.Delete method; it's Trendline.Delete:

mySeriesCol(i).Trendlines(1).Delete

Upvotes: 1

Related Questions