Results 1 to 4 of 4
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extracting range values from XY scatter chart (WinXP, O2003-UK)

    You can SET Values and XValues to a range or an array. But the range will be converted to an array of literal values, so when you retrieve Values or XValues, you always get an array.

    If you want to get at the ranges, look at the Formula (or FormulaR1C1) property of the series. you'll get something like

    =SERIES([Book1]Sheet1!$B$1,[Book1]Sheet1!$A$2:$A$5,[Book1]Sheet1!$B$2:$B$5,1)

    Look up SERIES in the Excel help.
    The first argument is the series name.
    The second argument specifies the source of the X values.
    The third argument specifies the source of the Y values.
    The fourth argument is the order of the series (1=first series etc.)

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting range values from XY scatter chart (WinXP, O2003-UK)

    Hi,

    Trying to extract range values from a XY scatter chart. Want to load X and Y ranges into two seperate strings for later usage. (strXrange and strYrange respectively)

    If I set it manually through the charts interface: 'Charts' - 'Source data' - 'Series' - 'Xvalues' (and records it as macro at the same time, it sets the property as: ActiveChart.SeriesCollection(1).xValues = "=Output!R3C2:R10C2"

    According to the 'help' it may be possible to extract the ranges:
    "Returns or sets an array of x values for a chart series. The XValues property can be set to a range on a worksheet or to an array of values, but it cannot be a combination of both. Read/write Variant."

    So I tried the following
    dim rngX as range, rngY as range

    rngX = Activechart.Seriescollection(1).Xvalues
    rngY = Activechart.Seriescollection(1).Values

    This one fails, if I then do it as a variant I always get an array returned with the actual values - not the range.

    Is it possible at all to have the range returned with this property?
    - or have I just misunderstood its usage?

    Are there better ways to easily extract each range or do I have to 'strip' the .seriesformula and locate each one - one at a time?

    All sorts of suggestions are more than welcome.

    TIA
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting range values from XY scatter chart (WinXP, O2003-UK)

    OK hans, thanks.

    I'm familiar with the series part - was just hoping for an easier way to cut some corners <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15>
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

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

    Re: Extracting range values from XY scatter chart (WinXP, O2003-UK)

    Sorry, no can do. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Posting Permissions

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