Macro for chart

  • Thread starter Thread starter Hamid
  • Start date Start date
H

Hamid

Hi,

How can I put the char's Series value as column value
instead of sheet name in the marco? I have 7 sheets
(Jan04,feb04...) and 8 charts on each sheet. I do not want
to change the value manually because it is very time
comsuming The macro has this value
ActiveChart.SeriesCollection(1).XValues = "=July04Cal!
R90C25:R120C25"

I like to use variable for "=July04Cal!" so when I change
the value at one place, all the values get the same value.

Thanks,
Hamid
 
Hamid -

You can't use a variable in the chart series formula. But you can get
around this by defining a dynamic range and using that in your chart.
Pardon my A! notation.

Suppose my data was in C3:C7 of a sheet. If I put the sheet name in A1
of Sheet3, I can define a dynamic range (press CTRL+F3) called
WhatRange, using this in the Refers To box:

=INDIRECT(Sheet3!$A$1&"!$C$3:$C$7")

To get this into the chart, in step 2 of the chart wizard, click on the
series tab, add a series, and enter this into the Y Values box:

=Sheet3!WhatRange

Excel will eventually change this to =Book1.xls!WhatRange, which means
you did it right. Now just change the sheet name in cell A1, and the
chart changes to reflect the changing data.

If you've got too much stuff hard coded in too many charts, you can try
my ChangeSeriesFormula add-in:

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

Enter a string (July04Cal) to replace and another (August04Cal) to
replace it, and you can change the active chart, or every chart on the
sheet. It's also handy if you want to look at different columns, for
example, so change $D$ to $E$.

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

Thanks for your replay!
I tried that one too, but I got this error
"Reference is not valid. Reference must be to an open
worksheet."

My sheet name is "July04Cal"

Thanks,
Hamid
 
Well, now you're complicating matters!

If you use Indirect, you can only refer to an open workbook. Same with
many other functions. You need a direct cell reference if the workbook
is closed.

- Jon
_______
 
So, there is no way to do it?
-----Original Message-----
Well, now you're complicating matters!

If you use Indirect, you can only refer to an open workbook. Same with
many other functions. You need a direct cell reference if the workbook
is closed.

- Jon
_______


.
Click to expand...
 
Hamid -

Here's an approach that will work. In a worksheet that's in the same
workbook as your chart, set aside an area for data to be charted. Copy
the range from one worksheet in the source workbook, then use Paste
Special from the Edit menu to paste links to the copied range. Use this
range as your chart's source data range. Assuming the data is configured
the same way in any potential source worksheet or workbook, you can use
Find-Replace to change the text in these links in worksheet cells
(manually or in code). When the text changes, the worksheet or workbook
referenced in the links changes, the data in the cells changes, and the
chart updates.

The add-in I mentioned can change the references in a chart series
formula, but it doesn't recognize a different closed workbook. It only
is able to switch to a workbook that's already open. When it comes to
manipulating links and ranges, charts aren't as smart as worksheets.

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