Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: change the range of a chart series' Values or XVal (2003 SP2)

    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.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: change the range of a chart series' Values or XVal (2003 SP2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •