Sometimes this works, and sometimes, for instance if formulas are producing
the data, it will hose the formulas. But if that were the case, you'd have
found another approach.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Bill Martin" wrote in message
...
Thanks Jon. The route I took was to check for the data first, and if it
does not exist then I dump in some blank data outside the normal range. A
kludge, but it works.

Bill
------------------------------
Jon Peltier wrote:
One way to deal with this problem is to temporarily change the chart type
of the series to area or column, then back to line or XY when you've
changed the values. This only affects line and XY charts, when the
existing values or xvalues range contains all blanks or unplottable
errors (in other words, if the series doesn't appear, you'll have this
problem).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Bill Martin" wrote in message
...
Problem resolved... For reasons that I don't understand, if a column of
data is blank then I get the problem that I've described. Testing in
advance for the presence of data, and dealing with it accordingly allows
me to avoid it.

Bill
-----------------------
Bill Martin wrote:
I'm trying to change a chart in Excel 2003 using VBA. Unfortunately
there's some non-reproducible error that's driving me crazy. I hope
someone here recognizes it.

What I did was to simply record a macro, and go through manually
resetting the chart settings. Then I copied that code to my program
and changed it a bit to have more flexibility. The thing is that it
works perfectly - mostly. Then it will go into stretches of crashing
every time I run it with an error message:

"Run-time error '1004': Unable to set the XValues property of the
Series class"

It does not seem to be my "improvements" to the code which cause the
problem since when my code starts to fail, the original code that the
macro recorder created also fails at the same point. Attached is the
code which runs and/or fails. At the moment it fails at the line
marked with '**** It's frustrating that all the lines above, which are
essentially identical, run ok until it hits the (3).XValues line. Or
sometimes that works and the (4).Values line might fail instead.

Sub MacroRecorderCreated()
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Calc!R6C1:R6000C1"
ActiveChart.SeriesCollection(1).Values = "=Calc!R6C5:R6000C5"
ActiveChart.SeriesCollection(2).XValues = "=Calc!R6C1:R6000C1"
ActiveChart.SeriesCollection(2).Values = "=Calc!R6C8:R6000C8"
ActiveChart.SeriesCollection(3).XValues = "=Calc!R6C1:R6000C1" '****
ActiveChart.SeriesCollection(3).Values = "=Calc!R6C12:R6000C12"
ActiveChart.SeriesCollection(4).XValues = "=Calc!R6C1:R6000C1"
ActiveChart.SeriesCollection(4).Values = "=Calc!R6C11:R6000C11"
ActiveChart.SeriesCollection(5).XValues = "=Calc!R6C1:R6000C1"
ActiveChart.SeriesCollection(5).Values = "=Calc!R6C11:R6000C11"
ActiveWindow.Visible = False
Windows("Stochasitcs2.xls").Activate
Range("J13").Select
End Sub

Does any of this look familiar?

Thanks.

Bill