Changing Series Name

[Guest]

I have a macro which amoung other things changes the name and values of an
embedded chart. When I run the macro for the first time everything works as
intended. If I rerun the macro, an error occurs at the .name statement. If
I comment out that line, everything else works fine. The same thing happens
if I have saved the file (after the error has occured once), and then re-open
the file and try to run the macro. The error message is

Run time error '1004':
Unable to set the Name property of the series class

Once the error has occured, the macro will never work correctly again unless
I create a new chart, and change the chart name in the macro. If I do this
the macro will work once, and upon subsequent running of the macro, the .name
error will occur.

The section of the code involved is:

k=1
While stock(k) <> ""
Sheets("Plot Period").Select
ActiveSheet.ChartObjects("Chart 14").Activate
With ActiveChart.SeriesCollection(k)
.Name = stock(k) 'this is the statement that causes the error
.XValues = "='" + stock(k) + "'!" + daterange
.Values = "='" + stock(k) + "'!" + gainrange
End With

k = k + 1

I have tried other forms of the code, with no success.

 

[okaizawa]

Hi,

when the error occurs, does the series have any data?
if the series doesn't have enough data and no data point is displayed,
it seems impossible to handle the series completely from macro.
to display data points, you should set any appropriate data in
XValues and Values before Name property, or set chart options
temporarily like this:

ActiveChart.DisplayBlanksAs = xlZero
ActiveChart.PlotVisibleOnly = False

 

[Guest]

There was data in the series (from the first time the macro ran) and I was
about to change the series values. You gave me a hint though, in other
macros I changed the series values first, and then the name, while this time,
I was changing the name first. Sure enough, when I moved the .name
instruction to the end, and did the .XValues and .Values first, the problem
went away. REALLY QUIRKY MICROSOFT!

Thanks very much for your response.