PDA

View Full Version : Solved: Error with Recorded Macro



herm5263
06-17-2010, 12:07 PM
I wanted to write macro to add a trendline to a stacked bar chart. To see what sample code might look like, I added a trendline to a scatter plot with Macro Recorder turned on. But recorded macro errors at .Add method out even tho' the recorded actions worked fine. ("Run-time error 80004005" , "Method 'Add' of object 'Trendlines' failed.") Can't seem to make it work. Also, trendline formatting does not record. Here is approx. recorded VBA code ---

Sub AddTrendline()
' Add Trendline to chart
' Keyboard Shortcut: Ctrl+Shift+L
'
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add ' Error 80004005
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection
.Type = xlPolynomial
.Order = 2
End With
With Selection
.Type = xlPolynomial
.Order = 5
End With
End Sub

mdmackillop
06-17-2010, 12:43 PM
Welcome to VBAX
Can you post a sample workbook. Use Manage Attachments in the Go Advanced reply section

herm5263
06-17-2010, 01:05 PM
Spreadsheet had sensitive financial data. So I pasted series values into new workbook (attached as Trendline_Test.xlsm), copied chart, and re-recorded macro. Now it works. So I'm confused why it doesn't work in original workbook.

herm5263
06-17-2010, 01:24 PM
I had made copy of stacked bar chart and then deleted all but one series, since Excel says you can't add trendline to stacked bars. But forgot to change name of the copy, so Excel likely got confused. Now I have to figure out how to add the trendline to the total of the stacked bars which is my original goal.

mdmackillop
06-17-2010, 01:30 PM
I guess just one of those thing.

You can lose a couple of redundant lines

Sub AddTrendline2()
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add Type:=xlPolynomial, Order:=6
End Sub