Copying trendine equation not working properly

Question

I would like to loop through four sets of data arranged in rows. I'd like to make a chart from each dataset and apply a trendline, let excel show the equation of the trendline and copy the "m" part of the equation of the trendline (y=mx+b) in a cell after the end of the row. I recorded a macro while doing the whole process with the first dataset and modified it a little to introduce the loop. My problem is that though the code creates the four charts with the trendlines and equations, but it copies the "m" value of the first chart after all the four lines. I tried to fix the problem, but failed. Now - in the same form, so I guess that it was the oroginal problem - this code prints after each dataset the first row of whatever is copied to the clipboarb from the code and after all the four datasets, and the remaining part of the copied part under it (only once). It might seem to make no sense, so it is best to try this code in the following way: Fill the range C3:K6 with numbers and run the code. After, copy the code to the clipboard and run the code again. So, my thwo questions are: 1. How to make the code to copy the "m" value of each dataset after them and 2. Why does it behave so crazy now?

Sub Lasttest()

Dim i As Integer

For i = 3 To 6
  Range("C" & i).Select
  ActiveCell.Range("A1:I1").Select
  ActiveSheet.Shapes.AddChart.Select
  ActiveChart.ChartType = xlXYScatter
  ActiveChart.SetSourceData Source:=ActiveCell.Range("Sheet1!A1:I1")
  ActiveChart.SeriesCollection(1).Select
  ActiveChart.SeriesCollection(1).Trendlines.Add
  ActiveChart.SeriesCollection(1).Trendlines(1).Select
  Selection.DisplayEquation = True
  ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
  ActiveCell.Offset(0, 10).Range("A1").Select
  ActiveSheet.Paste
Next

End Sub

Ferenc

Answer 1

Did some code cleanup and this works for me:

    Sub InsertChartsAndPrintEquations()

    Dim i As Integer
    Dim rng As Range

    For i = 3 To 6
      Set rng = Range("C" & i & ":K" & i)

      ' insert chart
      ActiveSheet.Shapes.AddChart.Select
      With ActiveChart
        .ChartType = xlXYScatter
        .SetSourceData Source:=rng
        With .SeriesCollection(1)
            .Trendlines.Add
            .Trendlines(1).DisplayRSquared = False
            .Trendlines(1).DisplayEquation = True
        End With

        ' grab & insert equation
        With ActiveSheet.ChartObjects(i - 2)
            .Activate
            Range("M" & i) = .Chart.SeriesCollection(1).Trendlines(1).DataLabel.Text
        End With
      End With
    Next

End Sub

Apparently, you have to use a range object when defining the source data and you have to activate the chart before you can grab the equation from it.

Edit #1

This code should be more robust:

Sub InsertChartsAndPrintEquations2()

    Dim i As Integer
    Dim rng As Range
    Dim cht As ChartObject

    ' add charts
    For i = 3 To 10
      Set rng = Range("C" & i & ":K" & i)
      ActiveSheet.Shapes.AddChart.Select

      With ActiveChart
        .ChartType = xlXYScatter
        .SetSourceData Source:=rng
        With .SeriesCollection(1)
            .Trendlines.Add
            .Trendlines(1).DisplayRSquared = False
            .Trendlines(1).DisplayEquation = True
        End With
      End With
    Next

    ' grab & insert equations
    i = 3 ' set to same starting value as in the for next loop above
    For Each cht In ActiveSheet.ChartObjects
        cht.Activate
        Range("M" & i) = cht.Chart.SeriesCollection(1).Trendlines(1).DataLabel.Text
        i = i + 1
    Next cht

End Sub