Search code examples
excelvbapivot-tablepivot-chart

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.

Solution

  • & 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