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

Question

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 want to see:

Any advice would be greatly appreciated.

Answer 1

& 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