Macro Works 3 Times Then Stalls

[almar_dc]

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

[Damon Ostrander]

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.

[Bob Umlas]

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

[almar_dc]

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 ]

[almar_dc]

Anybody Help? This one is driving me nuts!

Thanks