Sometimes, Excel doesn't give you the right formula. Did you copy the
formula and paste it in here? If so, this is an example of the problem.
There should not be a single quote after the name of the defined ranges
(before the following commas). I have encountered this problem when the
sheet name in the reference is "different", but I haven't explored it enough
to know what constitutes "different". Sometimes a space in the sheet name
will make it fail, and I suspect the underscore may also be implicated in
it.
If you know anything about VBA, perhaps you could insert a line that
replaces "'," (that's double quote - single quote - comma - double quote,
where the double quotes are enclosing the text to replace, not part of it)
with ",".
Maybe I'll play with that a bit, because I get an email every month or so
from a user who experiences this problem.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"kohai" wrote in message
...
Hi,
I have been trying to use John Peltier's awesome chart series change
procedure (Thanks a ton John!) but lately I am having problems that I
can't
seem to figure out why the chart series won't change.
I have line charts with 1 series per chart and they use defined names for
the date and data.
The macro reads the original formula as: (using debug.print)
=SERIES(,'gsci_rlm.xls'!'raw_weekly_Date','gsci_rl m.xls'!'raw_weekly_COL_E',1)
and I am trying to change the date to ...raw_weekly_3Date which is a valid
named range and works if I manually change the chart.
The new series after the substitution looks like :
=series(,'gsci_rlm.xls'!'raw_weekly_3Date','gsci_r lm.xls'!'raw_weekly_col_e',1)
I continually reciave the error "Unable to set Formula property of Series
Class"
I thought the problem might be associated with the single quotes b/c when
you look at the formula bar in Xcl, you don't see them. I'm stumped b/c
the
code looks straightforward and the names are valid, so why won't it work?
Your assistance is greatly appreciated!
Kohai