The source data for a graph in one of my workbooks has the following value in the
values field for the first name in the series.

=Archive!$A$2:$A$150

The second one being:-

=Archive!$B$2:$B$150

'Archive' is the worksheet where the data gets pasted from another worksheet namely
'Data' at a set time determined by the user in the code. This can be done by
entering a value in a cell on the 'Data' worksheet.

Would it be possible to change the value 150 in the above example for all in the
series (16)?

The above would change the value along the x axis. It would be helpful as some
events last for 60 incrementations and others 360.

It takes awhile to do it manually.

--

Why not used defined names

insert=Name=Define
Name: =MySeries1
Refersto: =Offset(Archive!$B$2,0,0,Count($B:$B),1)

then in the Chart, use

=Bookname!MySeries1


instead of
=Archive!$B$2:$B$150

Repeat for all series.

--
Regards,
Tom Ogilvy




"Saxman" wrote:


The source data for a graph in one of my workbooks has the following value in the
values field for the first name in the series.

=Archive!$A$2:$A$150

The second one being:-

=Archive!$B$2:$B$150

'Archive' is the worksheet where the data gets pasted from another worksheet namely
'Data' at a set time determined by the user in the code. This can be done by
entering a value in a cell on the 'Data' worksheet.

Would it be possible to change the value 150 in the above example for all in the
series (16)?

The above would change the value along the x axis. It would be helpful as some
events last for 60 incrementations and others 360.

It takes awhile to do it manually.

--

Tom Ogilvy wrote:

insert=Name=Define
Name: =MySeries1
Refersto: =Offset(Archive!$B$2,0,0,Count($B:$B),1)

Being only a novice, I think I can see roughly where you are coming from. Would the
above be inserted in the 'Data' worksheet code? Do I have to declare
strings/variables? How would I change the values? It would help if you described
in detail how I could acc omplish the above.

If I could utilise something like this, then the end of the graph would mean the end
of an event, thus obviating the need for a countdown timer.

then in the Chart, use

=Bookname!MySeries1


instead of
=Archive!$B$2:$B$150

Repeat for all series.



--

Tom Ogilvy wrote:

Why not used defined names

insert=Name=Define
Name: =MySeries1
Refers to: =Offset(Archive!$B$2,0,0,Count($B:$B),1)

then in the Chart, use

=Bookname!MySeries1


instead of
=Archive!$B$2:$B$150

Repeat for all series.

Good idea.

I tried '=MySeries1' and =Archive!$A$1 in 'name' and all I get is the following
error message 'That name is not valid'.

Where am I going wrong? Probably because I got some reference wrong.

It sounds like I need to use a name of my own, but which?

I really am thick!

I posted the following in Refers to:

=Offset(Archive!$B$2,0,0,Count($B:$B),1)

--

Did you get the error when defining the name, or when trying to apply it to
the chart?

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


"Saxman" wrote in message
...
Tom Ogilvy wrote:

Why not used defined names

insert=Name=Define
Name: =MySeries1
Refers to: =Offset(Archive!$B$2,0,0,Count($B:$B),1)

then in the Chart, use

=Bookname!MySeries1


instead of
=Archive!$B$2:$B$150

Repeat for all series.

Good idea.

I tried '=MySeries1' and =Archive!$A$1 in 'name' and all I get is the
following
error message 'That name is not valid'.

Where am I going wrong? Probably because I got some reference wrong.

It sounds like I need to use a name of my own, but which?

I really am thick!

I posted the following in Refers to:

=Offset(Archive!$B$2,0,0,Count($B:$B),1)

--

I often have to make changes like this, so I wrote a little utility to help:

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

I would replace $150 with 16, or whatever it might be. I use $ in the first
field to make sure it doesn't change 1150 as well. The utility doesn't work
reliably changing sheet names that contain spaces and other strange
characters, but it works great for cell addresses and simple sheet names.
(I've figured out how to make it work for complicated names, but have not
had time to follow up.)

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


"Saxman" wrote in message
...

The source data for a graph in one of my workbooks has the following value
in the
values field for the first name in the series.

=Archive!$A$2:$A$150

The second one being:-

=Archive!$B$2:$B$150

'Archive' is the worksheet where the data gets pasted from another
worksheet namely
'Data' at a set time determined by the user in the code. This can be done
by
entering a value in a cell on the 'Data' worksheet.

Would it be possible to change the value 150 in the above example for all
in the
series (16)?

The above would change the value along the x axis. It would be helpful as
some
events last for 60 incrementations and others 360.

It takes awhile to do it manually.

--

Jon Peltier wrote:

I often have to make changes like this, so I wrote a little utility to help:

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

I would replace $150 with 16, or whatever it might be.

I'm with that being a novice.

I use $ in the first field

Just '$' in the Name field?

to make sure it doesn't change 1150 as well. The utility doesn't work reliably
changing sheet names that contain spaces and other strange characters, but it
works great for cell addresses and simple sheet names. (I've figured out how to
make it work for complicated names, but have not had time to follow up.)

Are you saying that I should post your code in my 'Data' sheet.

How does one change the value?

Sorry to sound so dumb (I'm a novice), but I'm not exactly sure where to replace
references in your code.

Here are a few how-to links:

http://www.anthony-vba.kefra.com/vba/vbabasic1.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.cpearson.com/excel/codemods.htm

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


"Saxman" wrote in message
...
Jon Peltier wrote:

I often have to make changes like this, so I wrote a little utility to
help:

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

I would replace $150 with 16, or whatever it might be.

I'm with that being a novice.

I use $ in the first field

Just '$' in the Name field?

to make sure it doesn't change 1150 as well. The utility doesn't work
reliably
changing sheet names that contain spaces and other strange characters,
but it
works great for cell addresses and simple sheet names. (I've figured out
how to
make it work for complicated names, but have not had time to follow up.)

Are you saying that I should post your code in my 'Data' sheet.

How does one change the value?

Sorry to sound so dumb (I'm a novice), but I'm not exactly sure where to
replace
references in your code.

Jon Peltier wrote:

I often have to make changes like this, so I wrote a little utility to help:

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

I would replace $150 with 16,

No $? Just 16?

I would include the dollar sign with the number being sought, $150, but I
wouldn't bother with the number it's replaced by, since Excel automatically
inserts them in the series formulas.

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


"Saxman" wrote in message
...
Jon Peltier wrote:

I often have to make changes like this, so I wrote a little utility to
help:

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

I would replace $150 with 16,

No $? Just 16?

Jon Peltier wrote:

I often have to make changes like this, so I wrote a little utility to help:

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

I would replace $150 with 16, or whatever it might be. I use $ in the first field
to make sure it doesn't change 1150 as well. The utility doesn't work reliably
changing sheet names that contain spaces and other strange characters, but it
works great for cell addresses and simple sheet names. (I've figured out how to
make it work for complicated names, but have not had time to follow up.)

I was a bit confused with ChgSrsFmlaForm as it showed no menu. I found it
eventually. I have pasted my graph into your worksheet and used the Change Series
Function, but I get the following runtime error towards the end of the code with the
following line:-

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, strFrom, strTo)

I put the following string in 'Old String'

=Archive!$A$2:$A$150

I put the following in 'New String'

=Archive!$A$2:$A$150

I don't know why you had problems installing the utility.... Guess I'll have
to look into it. What Excel version?

Select the series, and look in the formula bar. That is the series formula,
which my utility operates on. You don't see

=Archive!$A$2:$A$150

anywhere in the series formula, so nothing changes. However, you probably
see something like

$A$2:$A$150

and using the utility you could change it to

$B$4:$B$200

if you were so inclined. To change $A$2:$A$150 to $B$2:$B$150, simply use
$A$ as the old string and B as the new string.

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


"Saxman" wrote in message
...
Jon Peltier wrote:

I often have to make changes like this, so I wrote a little utility to
help:

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

I would replace $150 with 16, or whatever it might be. I use $ in the
first field
to make sure it doesn't change 1150 as well. The utility doesn't work
reliably
changing sheet names that contain spaces and other strange characters,
but it
works great for cell addresses and simple sheet names. (I've figured out
how to
make it work for complicated names, but have not had time to follow up.)

I was a bit confused with ChgSrsFmlaForm as it showed no menu. I found it
eventually. I have pasted my graph into your worksheet and used the
Change Series
Function, but I get the following runtime error towards the end of the
code with the
following line:-

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, strFrom,
strTo)

I put the following string in 'Old String'

=Archive!$A$2:$A$150

I put the following in 'New String'

=Archive!$A$2:$A$150

Jon Peltier wrote:

I don't know why you had problems installing the utility.... Guess I'll have to
look into it. What Excel version?

Select the series, and look in the formula bar. That is the series formula, which
my utility operates on. You don't see

=Archive!$A$2:$A$150

anywhere in the series formula, so nothing changes. However, you probably see
something like

$A$2:$A$150

and using the utility you could change it to

$B$4:$B$200

if you were so inclined. To change $A$2:$A$150 to $B$2:$B$150, simply use $A$ as
the old string and B as the new string.

Thanks for the feedback. I am using Excel 2003. I don't have trouble installing
it, it's knowing what to do with it from a layman's point of view.

How do I apply your 'Sheet 1' worksheet to another workbook (containing a graph)?

I have pasted a graph into 'Sheet 1' of your workbook, entered new values and tried
to change them using your form, but I get a runtime error.

I have also copied your code and pasted it into my workbook, but I am unable to run
it, as there is no way to activate the form (as I have explained in another posting).

It might be easier saving several workbooks with different values and applying each
one to different situations? Sounds laborious, but so is changing code about 16
times in a worksheet.

Thanks again.

You're making it too hard on yourself. You don't have to copy and paste any
code. Just use the button on the floating toolbar to use the code on any
sheet.

The interface couldn't be more straightforward. Whenever you have a find and
replace, there's an old text string you want to change to a new text string.
Enter the old string into the Old String box, and the new string into the
New String box. The options and buttons are self-explanatory.

If you ask the program to insert code that invalidates the series formula,
the formula is not changed. Clear the error, and try something else.
Changing a comma to a semicolon, for example, will cause this type of error,
or changing a column address ($A$) to something invalid (3) will also fail,
as will changing the sheet name to the name of a nonexistent sheet. Valid
sheet names that contain spaces and other special characters also lead to
failure due to incongruities in the VBA Charting object model. I have
workarounds in mind, but I have not had an opportunity to implement them in
this free utility.

In any case, I've made some changes to the utility, in hopes it's even
easier to use, more self-explanatory, and tolerant of errors in new series
formula strings. I've updated this page to describe it:

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

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


"Saxman" wrote in message
...
Jon Peltier wrote:

I don't know why you had problems installing the utility.... Guess I'll
have to
look into it. What Excel version?

Select the series, and look in the formula bar. That is the series
formula, which
my utility operates on. You don't see

=Archive!$A$2:$A$150

anywhere in the series formula, so nothing changes. However, you probably
see
something like

$A$2:$A$150

and using the utility you could change it to

$B$4:$B$200

if you were so inclined. To change $A$2:$A$150 to $B$2:$B$150, simply use
$A$ as
the old string and B as the new string.

Thanks for the feedback. I am using Excel 2003. I don't have trouble
installing
it, it's knowing what to do with it from a layman's point of view.

How do I apply your 'Sheet 1' worksheet to another workbook (containing a
graph)?

I have pasted a graph into 'Sheet 1' of your workbook, entered new values
and tried
to change them using your form, but I get a runtime error.

I have also copied your code and pasted it into my workbook, but I am
unable to run
it, as there is no way to activate the form (as I have explained in
another posting).

It might be easier saving several workbooks with different values and
applying each
one to different situations? Sounds laborious, but so is changing code
about 16
times in a worksheet.

Thanks again.

Saxman wrote:

I was a bit confused with ChgSrsFmlaForm as it showed no menu. I found it
eventually. I have pasted my graph into your worksheet and used the Change Series
Function, but I get the following runtime error towards the end of the code with
the following line:-

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, strFrom, strTo)

I put the following string in 'Old String'

=Archive!$A$2:$A$150

I put the following in 'New String'

=Archive!$A$2:$A$150



The above error was probably because my graph did not carry the series data into
your graph when it was copied/pasted.

I have now done a role reversal and successfully imported your form and module into
the correct locations within my workbook. However, I cannot execute the form, as I
cannot locate a command button from my options, probably because this is a custom
control? Is there a way around this?

TIA.

--