How do I extract the data label of a trendline in VBA?

[RockandGrohl]

Hi guys, have done way too much searching on this, even wrestling with ChatGPT which is spinning me round in circles.

I have a chart called "CurveChart", and when I select information from 2 drop down boxes it populates a simple scatter chart with the data points, and a linear trendline, which then has the trendline Equation displayed as a data label.

What I'm trying to do is to get that Trendline equation to be input into cell H3 on the same sheet.

Below is my code. I am getting "parameter not valid"

My chart only has one series, and one trendline. When the drop-down boxes are filled in, it filters a table on Database (db) tab which then populates the series in the chart.

Private Sub worksheet_change(ByVal Target As Range)

Dim Keycells As Range
Dim cur As Worksheet, calc As Worksheet, db As Worksheet
Dim strFormula As String, asset As String, inter As String
Dim Lastrow As Long
Dim chart As ChartObject
Dim series As series
Dim trendline As trendline


Set Keycells = Range("B3:H5")
Set cur = Worksheets("Curves")
Set db = Worksheets("Database")
Set calc = Worksheets("Calculations")

Application.ScreenUpdating = False

If Not Intersect(Range("B3"), Target) Is Nothing Then
    Application.EnableEvents = False
    If Range("B3").Value = "" Then
    On Error Resume Next
    db.ShowAllData
    On Error GoTo 0
    cur.Range("B5").Value = ""
    Else
        If Not Range("B5").Value = "" Then
        Range("B5").Value = ""
        End If
       
    On Error Resume Next
    db.ShowAllData
    On Error GoTo 0
   
    calc.Range("M2:N500").ClearContents
    calc.Range("I2:I500").ClearContents
    asset = Range("B3").Value
    ' Disable line on chart
    Lastrow = db.Cells(Rows.Count, "A").End(xlUp).Row
    db.Range("H1").AutoFilter 8, asset
    calc.Range("H2").Value = asset
    'Add count in here to check visible rows exist
    db.Range("K2:K" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
    calc.Range("I2").PasteSpecial (xlPasteValues)
    cur.ChartObjects("CurveChart").Activate
    ActiveChart.ChartType = xlXYScatter
    End If
End If
Application.EnableEvents = True


        If Not Intersect(Range("B5"), Target) Is Nothing Then
        inter = Range("B5").Value
        calc.Range("M2:N500").ClearContents
        On Error Resume Next
        db.ShowAllData
        On Error GoTo 0
        Lastrow = db.Cells(Rows.Count, "A").End(xlUp).Row
        asset = cur.Range("B3").Value
        db.Range("A1:AG" & Lastrow).AutoFilter 8, asset
        db.Range("A1:AG" & Lastrow).AutoFilter 11, inter
        db.Range("A1:AG" & Lastrow).AutoFilter 20, "<>"
        'db.Range("T2:T" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        'calc.Range("M2").PasteSpecial (xlPasteValues)
        'db.Range("P2:P" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        'calc.Range("N2").PasteSpecial (xlPasteValues)
        cur.ChartObjects("CurveChart").Activate
        ActiveChart.ChartType = xlXYScatter
        'strFormula = ActiveSheet.ChartObjects("CurveChart").SeriesCollection(1).Trendlines(1).DataLabel.Text
        'strFormula = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
        Range("H3").Value = strFormula
        Else
        cur.ChartObjects("CurveChart").Activate
        ActiveChart.ChartType = xlXYScatter
        End If
       
       
Application.ScreenUpdating = True
End Sub

The part of the code that's erroring out is the strFormula lines near the bottom - neither of these seem to work. Whenever one works, I would simply drop that value into cell H3.


Cheers!

 

[RockandGrohl]

Hopefully this helps to clarify it.

Users select Asset, then Intervention.

When the Intervention flicks between New & Replace, it should be changing the Trendline Data Label, and the Cost Curve field should reflect that data label.

The Data label is correct - on Replace, which has two data points, it should be 296.01x + 27790. Instead, the Cost Curve field shows y= 79.496 etc, which is the correct trendline formula for "New" Intervention.


If I step through the code, it works flawlessly.

 

[RoryA]

Do you remember when I suggested it was better/easier to calculate it yourself?

 

[RockandGrohl]

Do you remember when I suggested it was better/easier to calculate it yourself?

Haha, but my friend, I am!!

The Trendline calculation that Excel applies to the chart is 100% correct.

But the manual calculation I do here:

        calc.Range("Q2:R2") = WorksheetFunction.linest(calc.Range("N2:N" & RowCount + 1), calc.Range("M2:M" & RowCount + 1), True)

Seems to lag behind.

Look at the order here:

    RowCount = db.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    If RowCount > 1 Then
        db.Range("T2:T" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        calc.Range("M2").PasteSpecial (xlPasteValues)
        db.Range("P2:P" & Lastrow).SpecialCells(xlCellTypeVisible).Copy
        calc.Range("N2").PasteSpecial (xlPasteValues)
        DoEvents
        Application.Wait (Now + TimeValue("0:00:01"))
        calc.Range("Q2:R2") = WorksheetFunction.linest(calc.Range("N2:N" & RowCount + 1), calc.Range("M2:M" & RowCount + 1), True)
        With cur
        DoEvents
            With .ChartObjects(1).chart.SeriesCollection(1).Trendlines(1)
                strFormula = .DataLabel.Text
            End With
            Application.EnableEvents = False
            DoEvents
            .Range("H3").Value = strFormula
            Application.EnableEvents = True
        End With
        End If

Prior to this section, the code filters the db tab based on the user inputs, and the chart is updated with the correct value on the trendline formula
calc tab gets the same db inputs pasted as text, linest is performed in cell Q2:R2, and the correct value is written
The next step is to then pass the value in datalabel.text into a string and paste that string in the cell, but this seems to get stuck on whatever I have selected in the chart inputs before. Very frustrating.

 

[RoryA]

Why are you reading the data label if you already calculated the values?

 

[RockandGrohl]

Why are you reading the data label if you already calculated the values?

I don't know, Rory. I just don't know.

It's sorted now, I just use the calculated value each time which is done in a separate sheet. Not the cleanest way to do it, but it'll work. Sheet is now working beautifully.