Let me elaborate on Chip's response. The chart series formula contains
addresses of the ranges of data in the chart, but the Values and XValues
are not themselves ranges, they are the arrays of values in these
ranges. If you want to manipulate the ranges you will need to parse the
series formula, figure out what's between which commas, and therefore
which has the data you want.

John Walkenbach has a class module that does this for you much more easily:

http://www.j-walk.com/ss/excel/tips/tip83.htm

If all you want to do is refer to the same range of cells on a different
sheet, you could use the Change Series Formula macros on my web site:

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

You can simply change the text from 'Sheet1' to 'Sheet2', or perhaps a
column from $A to $B, to update a chart.

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

Maynard wrote:
I'm attempting to set up a function that will update a chart based on
allowing the user to select which sheet the data is on. There are several
series on each chart, so I'm trying to avoid using named ranges. I can't
seem to figure out why this isn't working. I get "object variable or with
block variable not set" error on the specified line. Thanks for any help!
PS - strShtName is entered on a UserForm.

Dim Rng As Range
Dim Cht As Chart
Dim ChtSeries As Series

For Each Cht In ThisWorkbook.Charts
For Each ChtSeries In Cht.SeriesCollection
Rng = ChtSeries.Values 'ERROR ON THIS LINE!
Rng.Parent.Name = strShtName
Rng = ChtSeries.XValues
Rng.Parent.Name = strShtName
Set Rng = Nothing
Next ChtSeries
Next Cht