Macro Works 3 Times Then Stalls

almar_dc

New Member
Joined
Mar 26, 2002
Messages
8
Hi,

I am new to Macro's & VBA so I could use any help or advice. When I run the macro it works 3 times then fails what am I doing wrong?

Sub ShowChart()
Calculate
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(1).Formula = _
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(1).Formula
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(2).Formula = _
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(2).Formula
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(3).Formula = _
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(3).Formula
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(4).Formula = _
ActiveSheet.ChartObjects("Chart 5").Chart.SeriesCollection(4).Formula

DialogSheets("Chart").Show

End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi Alan,

What do you mean when you say it "fails"? Do you get a run-time error? If so, is there an error message? Can you go into the debugger and find which line of code it is failing on? The answers to these questions would be helpful in diagnosing the problem.

Also, in looking at your code, I'm wondering why you are setting each series' formula to itself. This should not actually accomplish anything.

In addition, the use of Dialog sheets (as opposed to userforms) is no longer recommended in versions of Excel from 97 on up.

It might be helpful if you would describe what you are actually trying to accomplish. There may be a better way.
 
Upvote 0
Might I also add that you can make your code a LOT easier to read & debug by changing it to:
Sub ShowChart()
Calculate
For i=1 to 4
With ActiveSheet.ChartObjects("Chart 5").Chart
.SeriesCollection(i).Formula = _
.SeriesCollection(i).Formula
End With
Next
DialogSheets("Chart").Show
End Sub
 
Upvote 0
Thanks for the replys, like I said vb is all Russian to me. The chart is based on a large table of costs spread over a year there are 4 dataseries associated with each project and lots of projects. The chart uses relative range names for the title,x values & 4 data series as follows;
=SERIES(IIS_S.xls!name3,IIS_S.xls!XLabels,IIS_S.xls!Dataseries3,3) etc.
Now comes the interesting bit....when you create a new chart and put in the ranges as above it works 3 times then it stops with "Runtime error 1004" "Unable to set the formula property of the series class" (the code stops at the Series(4) lines of code). If I delete the chart and start again it works for 3 more times.
Bob, I tried your code and it works but stops at same place as before. Help!

Regards
This message was edited by almar_dc on 2002-03-28 12:08
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top