Graph Source Data

[Saxman]

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.

--

 

[Guest]

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.

 

[Jon Peltier]

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

 

[Saxman]

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.

--

 

[Saxman]

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.

 

[Saxman]

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]

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]

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

 

[Saxman]

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

 

[Saxman]

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)

--

 

[Jon Peltier]

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

- Jon

 

[Jon Peltier]

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

 

[Saxman]

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.

--

 

[Saxman]

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.

 

[Jon Peltier]

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

 

[Saxman]

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.

I got the new add-in, thanks. No problems with integrating it into my workbook.
Well done.

My single graph is pasted onto the 'Data' worksheet. The chart gets it data from
the 'Archive' worksheet.

I have the following formula in the 'Name field' of my graph.
=Archive!$A$1

I have the following formula in the 'Values' field.

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

As stated previously I would like to change the last 3 digits in the 'Values' field,
say to,

=Archive!$A$2:$A$170 for the whole series, and quite naturally =Archive!$B$2:$B$170
for the second in the series.

Utilising your utility, I paste, =Archive!$A$2:$A$150 into the 'Old String' field and
=Archive!$A$2:$A$170 into the 'New String' field. Clicking OK produces the
following run-time error.

Run time error '1004'. Unable to get the formula property of the Series class.

The line in the code producing the error is below.

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

Sorry to sound so dumb, but I do not normally meddle with such things, but I'm
learning, thanks to you!

 

[Jon Peltier]

I got the new add-in, thanks. No problems with integrating it into my

workbook.
Well done.

This doesn't mean you copied code into your workbook, right? That's what you
were doing before and it wasn't necessary. It's especially not necessary
now. Just activate the sheet with your charts and use the buttons.

As stated previously I would like to change the last 3 digits in the
'Values' field,

If you were doing a find-replace in the worksheet for three characters, you
would change just those three characters, right? You want to change row 150
to row 170 in the cell references. So in the Find box, enter:

$150

and in the Replace With box, enter:

$170.

This changes all instances of $150 in all series formulas in the active
chart, meaning all X values and all Y values for all relevant series.

The line in the code producing the error is below.
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, strFrom,
strTo)

This line has been altered in the new version of the utility to allow for a
graceful exit in the event of this kind of error. Are you sure you have
removed the old version of the utility?

The program now uses an intermediate variable sFormula, splitting the above
line into two lines:

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

- Jon

 

[Saxman]

If you were doing a find-replace in the worksheet for three characters, you would
change just those three characters, right? You want to change row 150 to row 170
in the cell references. So in the Find box, enter:

$150

and in the Replace With box, enter:

$170.

I'll give it a whirl later on and let you know. I'll use a fresh copy so I can
eliminate code corruption.

Thanks.

 

[Saxman]

This doesn't mean you copied code into your workbook, right? That's what you were
doing before and it wasn't necessary. It's especially not necessary now. Just
activate the sheet with your charts and use the buttons.


If you were doing a find-replace in the worksheet for three characters, you would
change just those three characters, right? You want to change row 150 to row 170
in the cell references. So in the Find box, enter:

$150

and in the Replace With box, enter:

$170.

This changes all instances of $150 in all series formulas in the active chart,
meaning all X values and all Y values for all relevant series.


This line has been altered in the new version of the utility to allow for a
graceful exit in the event of this kind of error. Are you sure you have removed
the old version of the utility?

The program now uses an intermediate variable sFormula, splitting the above line
into two lines:

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

I tried the above on a fresh copy of my spreadsheet. No installation problems.
However, I do get the following message box after entering data into your table.

Unknown error

Unable to get the FORMULA property of the series class

The worksheet appears to function OK after entering new values though.

It does not change the values for <blank series> in the series, which is probably
why I get an error message.

I do get <blank series>, as most events have differing numbers.

I have a piece of code that copies/pastes the names in the series and places them in
the 'Archive' worksheet. These are then placed at the foot of the graph in a legend
for reference.

 

[Saxman]

I do get <blank series>, as most events have differing numbers.

I have a piece of code that copies/pastes the names in the series and places them
in the 'Archive' worksheet. These are then placed at the foot of the graph in a
legend for reference.

I do get <blank series>, as most events have differing numbers.

I have a piece of code that copies/pastes the names in the series and places them
in the 'Archive' worksheet. These are then placed at the foot of the graph in a
legend for reference.

I've had another go at this and all references get changed until the code gets to a
<blank series>. I have 16 different inputs in my series. If I am gathering data
for an event with 5 inputs, the rest appear as <blank series> (6-16).

If I save the document, re-open it and then use your string conververter, quite
naturally, only the first 5 will get converted, as they are the only inputs with
labels and the rest appear as <blank series> when the document was saved.

If I then choose an event with 10 labels, naturally I get 10 labels, but only the
first 5 get converted.

It's a pity the code will not recognise <blank series> and convert the data.