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

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

Well, I did a little bit of checking, and what seems to fail the code is
when the X values are defined by a name instead of a range address (Excel
doesn't mind a name in the Y values). Excel insists on putting the single
quote around the name in MySrs.Formula. If you leave the single quotes in or
take them out, you still get an error when you try to set the new formula of
the series. I even recorded a macro while I manually changed the formula,
and those single quotes sneaked in:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 7/31/2007 by Jon Peltier

'

ActiveChart.SeriesCollection(1).Formula = _

"=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)"

End Sub


I noticed that the macro recorded the first argument in R1C1 notation, but
when I made that adjustment, the code still failed.

This here's a bug, folks. But don't be alarmed, I have an idea for a
workaround.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
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

My workaround seems successful. I had to steal some code from John
Walkenbach, mostly because I was too lazy to write a parser for the chart
series formula. It's not well tested yet, so I haven't posted it. maybe
tomorrow.

Essentially, the code now parses the formula, and if the XValues consist of
a name, it temporarily removes the XValues from the formula, does the
replacement, then reinserts the XValues back using

.XValues = "=" & /previous XValues string/

Fortunately .XValues is not plagued by the excess single quotes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
Well, I did a little bit of checking, and what seems to fail the code is
when the X values are defined by a name instead of a range address (Excel
doesn't mind a name in the Y values). Excel insists on putting the single
quote around the name in MySrs.Formula. If you leave the single quotes in
or take them out, you still get an error when you try to set the new
formula of the series. I even recorded a macro while I manually changed
the formula, and those single quotes sneaked in:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 7/31/2007 by Jon Peltier

'

ActiveChart.SeriesCollection(1).Formula = _

"=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)"

End Sub


I noticed that the macro recorded the first argument in R1C1 notation, but
when I made that adjustment, the code still failed.

This here's a bug, folks. But don't be alarmed, I have an idea for a
workaround.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
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

I've posted the updated utility to my web site and filed a bug report.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
My workaround seems successful. I had to steal some code from John
Walkenbach, mostly because I was too lazy to write a parser for the chart
series formula. It's not well tested yet, so I haven't posted it. maybe
tomorrow.

Essentially, the code now parses the formula, and if the XValues consist
of a name, it temporarily removes the XValues from the formula, does the
replacement, then reinserts the XValues back using

.XValues = "=" & /previous XValues string/

Fortunately .XValues is not plagued by the excess single quotes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
Well, I did a little bit of checking, and what seems to fail the code is
when the X values are defined by a name instead of a range address (Excel
doesn't mind a name in the Y values). Excel insists on putting the single
quote around the name in MySrs.Formula. If you leave the single quotes in
or take them out, you still get an error when you try to set the new
formula of the series. I even recorded a macro while I manually changed
the formula, and those single quotes sneaked in:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 7/31/2007 by Jon Peltier

'

ActiveChart.SeriesCollection(1).Formula = _

"=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)"

End Sub


I noticed that the macro recorded the first argument in R1C1 notation,
but when I made that adjustment, the code still failed.

This here's a bug, folks. But don't be alarmed, I have an idea for a
workaround.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
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

John,

Thank you very much for your assistance. The knowledge that you have kindly
imparted to the me and others has been invaluable.

I had an idea that the single quotes might have been a problem, but I will
take a look at your website and try and implement the fix that you've created.

How would we know if the bug is fixed in the future?

Regards,

Kohai


"Jon Peltier" wrote:

I've posted the updated utility to my web site and filed a bug report.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
My workaround seems successful. I had to steal some code from John
Walkenbach, mostly because I was too lazy to write a parser for the chart
series formula. It's not well tested yet, so I haven't posted it. maybe
tomorrow.

Essentially, the code now parses the formula, and if the XValues consist
of a name, it temporarily removes the XValues from the formula, does the
replacement, then reinserts the XValues back using

.XValues = "=" & /previous XValues string/

Fortunately .XValues is not plagued by the excess single quotes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
Well, I did a little bit of checking, and what seems to fail the code is
when the X values are defined by a name instead of a range address (Excel
doesn't mind a name in the Y values). Excel insists on putting the single
quote around the name in MySrs.Formula. If you leave the single quotes in
or take them out, you still get an error when you try to set the new
formula of the series. I even recorded a macro while I manually changed
the formula, and those single quotes sneaked in:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 7/31/2007 by Jon Peltier

'

ActiveChart.SeriesCollection(1).Formula = _

"=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)"

End Sub


I noticed that the macro recorded the first argument in R1C1 notation,
but when I made that adjustment, the code still failed.

This here's a bug, folks. But don't be alarmed, I have an idea for a
workaround.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
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

John,

I've looked at the updated utility and thank you for addressing the error.
Just to clarify, I see you've made a change to error handler to show that the
formula cannot be changed, but until MS makes some sort of change to excel,
there is no other way to programitically alter the series as it now stands?

I thought from your earlier post that you figured out how to make it work.

Thank you for assistance.

Kohai



"Jon Peltier" wrote:

I've posted the updated utility to my web site and filed a bug report.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
My workaround seems successful. I had to steal some code from John
Walkenbach, mostly because I was too lazy to write a parser for the chart
series formula. It's not well tested yet, so I haven't posted it. maybe
tomorrow.

Essentially, the code now parses the formula, and if the XValues consist
of a name, it temporarily removes the XValues from the formula, does the
replacement, then reinserts the XValues back using

.XValues = "=" & /previous XValues string/

Fortunately .XValues is not plagued by the excess single quotes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
Well, I did a little bit of checking, and what seems to fail the code is
when the X values are defined by a name instead of a range address (Excel
doesn't mind a name in the Y values). Excel insists on putting the single
quote around the name in MySrs.Formula. If you leave the single quotes in
or take them out, you still get an error when you try to set the new
formula of the series. I even recorded a macro while I manually changed
the formula, and those single quotes sneaked in:

Sub Macro1()

'

' Macro1 Macro

' Macro recorded 7/31/2007 by Jon Peltier

'

ActiveChart.SeriesCollection(1).Formula = _

"=SERIES('Sheet 1'!R3C3,Book1!'categories',Book1!Beta,1)"

End Sub


I noticed that the macro recorded the first argument in R1C1 notation,
but when I made that adjustment, the code still failed.

This here's a bug, folks. But don't be alarmed, I have an idea for a
workaround.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" wrote in message
...
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