Question on setting names and values of SeriesCollection

[mchow2469]

Hi,

Right now I get a runtime error when modifying my chart through VBA:
Unable to set name property of Series Class.

Here is the code that causes it:

With mychart        
        'plot average
        .SeriesCollection(6).Name = "=""Average"""
        .SeriesCollection(6).Values = "=CSS!R10C" & avgCol & ":R54C" & avgCol
    End With

The name and values of the series collection of my chart is already defined before this code is ran ( by right click chart |source data option).
Is it possible to change the name and values again in VBA? Because this is exactly what I am trying to do, which is causing the run time error.

If it is not possible to change the name and values of the series collection again, then how would I work around this issue? Thanks!

[Ecniv]

Make up a sub that has some variables set to different objects in the chart. Once these are set, put a debug.print line in and place a breakpoint on it.

Run the sub (f5?) and when it hits the breakpoint, press ctrl+G (puts you to the immediates window). Then try setting some of the properties manually through code in there.
Alternatively, if you think that the code you have is ok, use f1 on the property and see if there is anything in the help files for it.

[mchow2469]

Thanks for the info Ecniv, but I still can not figure out why the compiler can not access the SeriesCollection(6) and SeriesCollection (7), which is the case.

Here is hte complete block where I alter the chart data:

With mychart
        .Axes(xlCategory, xlSecondary).TickLabelSpacing = 1
        .Axes(xlCategory, xlPrimary).TickLabelSpacing = 1
        'plot secondary x axis values
        .SeriesCollection(1).XValues = ""
        'MsgBox ("=CSS!R10C" & secaxisCol & ":R54C" & secaxisCol)
        .SeriesCollection(1).XValues = "=CSS!R10C" & secaxisCol & ":R54C" & secaxisCol
        
        'plot primary x axis values
        .SeriesCollection(2).XValues = ""
        .SeriesCollection(2).XValues = "=CSS!R10C" & xaxisCol & ":R54C" & xaxisCol
        
        'plot average
        '.SeriesCollection(6).Name = "=""Average"""
        .SeriesCollection(6).Values = ""
        .SeriesCollection(6).Values = "=CSS!R10C" & avgCol & ":R54C" & avgCol
        
        'plot standard deviation
        
        '.SeriesCollection(7).Name = "=""STD"""
        .SeriesCollection(7).Values = ""
        .SeriesCollection(7).Values = "=CSS!R10C" & stdCol & ":R54C" & stdCol
    End With

Here, when I change seriescollection 1 and 2's values, it is ok. Those series are the primary and secondary x axis labels, respectively.
SeriesCollection 3 - 5 are just constant data points I plotted on the graph, which do not need to be altered.
But when I try to change the seriescollection 6 and 7, I get the runtime error. SeriesCollection 6 and 7 represents the data I want to plot and change through time. Again, all the series were predefined by using the 'source data' option when creating the chart, so adding SeriesCollection.NewSeries will not do anything.

Any help would be greatly appreciated. Thansk!

[mchow2469]

oops, accidently posted on wrong thread sorry!

btw when I add debug.print statements,
It cant even print to the immediate window because it has a runtime error when executing the debug.print statement.

There is a problem accessing the SeriesCollection 6 and 7 values and names.