Adding trendlines to a chart while using a macro

**cainer** · 07-25-2009, 02:32 PM

I keep having the same problem occur no matter what I try. When using a macro to provide a chart from a given data set, the trendline that I am trying to use along with the chart will not work and keeps giving a runtime error. Any hints???

**shg** · 07-25-2009, 04:12 PM

Welcome to the forum.

Post the workbook with your code.

**cainer** · 07-25-2009, 04:21 PM

This is what im trying to chart with a exponential trendline using a macro. Its a simple task but I just can't figure out what I'm missing. This is the data that I'm using.

Year Sales
1 70
2 183
3 340
4 649
5 1243
6 1979
7 4096
8 6440
9 8459
10 12154

Sub ExpTrendChart()
'
' ExpTrendChart Macro
'

'
Range("A1:B11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'12.19'!$A$1:$B$11")
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add
ActiveSheet.ChartObjects("Chart 11").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
Selection.Type = xlExponential
End Sub

**shg** · 07-25-2009, 04:34 PM

Please take a few minutes to read the forum rules, and then edit you post to add code tags.

**cainer** · 07-25-2009, 04:39 PM

This is what im trying to chart with a exponential trendline using a macro. Its a simple task but I just can't figure out what I'm missing. This is the data that I'm using.


Year	Sales
1	70
2	183
3	340
4	649
5	1243
6	1979
7	4096
8	6440
9	8459
10	12154

Sub ExpTrendChart()
'
' ExpTrendChart Macro
'

'
    Range("A1:B11").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'12.19'!$A$1:$B$11")
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add
    ActiveSheet.ChartObjects("Chart 11").Activate
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    Selection.Type = xlExponential
End Sub

**Andy Pope** · 07-26-2009, 07:14 AM

If you remove the code that specifies a chart to activate then it works,
That line would have been correct once but subsequent runs of the macro would mean the chart name changes.

    ActiveSheet.ChartObjects("Chart 11").Activate

Sub ExpTrendChart()
'
' ExpTrendChart Macro
'

'
    Range("A1:B11").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'12.19'!$A$1:$B$11")
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    Selection.Type = xlExponential
End Sub