SeriesCollection

[Varun]

Hello, let me give a little background first. I'm updating
an excel spreadsheet that goes by fiscal quarters. I have
the user enter his start quarter and end quarter and then
I want to have a graph automatically update for the user's
entered data range. I have been trying to use the
SeriesCollection, .Values and .Formula to be more
specific. However, every time I try to set SeriesCollection
(1).Values or SeriesCollection(1).Formula equal to
something, I get a 1004 runtime error. It says "Unable to
set the Values property of the Series class." What data
type are .Values and .Formula supposed to be? Right now,
mine are strings that, in excel, would be formulas when
entered into a cell. Does anyone know any other way to
change the data in an individual series in a graph? Any
help would be appreciated. Thanks!

 

[Jon Peltier]

The series formula must be a formula that properly references the values
and xvalues as either an array or a worksheet range. If you want to
produce a function, you must first create a defined range name that
holds the values of the function, then use this defined name in the
series formula.

The values and xvalues of a series also must refer to arrays or
worksheet ranges.

You might first want to refer to Stephen Bullen's non-VBA technique for
plotting a formula. The defined names contain the X and Y values, as
arrays, and these are referred to in the series formula.

http://bmsltd.ie/DLCount/DLCount.asp?file=ChtFrmla.zip

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