-
Solved: Error with Recorded Macro
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 ---
[VBA]
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
[/VBA]
-
Welcome to VBAX
Can you post a sample workbook. Use Manage Attachments in the Go Advanced reply section
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Trendline works now!
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.
-
found one mistake
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.
-
I guess just one of those thing.
You can lose a couple of redundant lines
[vba]
Sub AddTrendline2()
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add Type:=xlPolynomial, Order:=6
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules