change the range of a chart series’ Values or XVal (2003 SP2)

Topic

New Reply

Aloha all,
I’m trying to write code to quickly change the range that a chart series refers to. I’m thinking that I should be able to use the Offset method if I can grab the Values as a range. But when I try to get at them, I get an array of values, not a range object or address string. If the series formula is

=SERIES(table!$A$6,table!$C$5:$L$5,table!$C$6:$L$6,1)

then ideally I would use a line like

activechart.SeriesCollection(1).Values = activechart.SeriesCollection(1).Values.offset(50,0)

to shift the series to refer to cells 50 rows down. But the code

activechart.SeriesCollection(1).Values

returns an array, typename Variant(). Grrr! The Values property can be set by using either a range on a worksheet or an array of values, but apparently it only returns an array, not a range. Do I have to parse out the series formula and edit each occurrence of the row number?

Mahalo for your comments,
JohnJ

Reply | Quote

Replies

As you have found, the Values property contains an array, not a range. After all, you don’t need to base a chart on a range, you can create one from data entered literally. So you’ll have to look at the Formula property.

Reply | Quote

Thank you Hans, string manipulation it is then. Thank God my data in this case is in rows, not columns.
Aloha,
JohnJ

Reply | Quote