Can not set Y Values in XY chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

L Mehl - 01 Feb 2004 01:54 GMT

Hello --

I use the folliwing code to set X and Y values:

g_strRangeSeries = "A2:A82" (calculated in prior code)
strDataXValues = Chr(34) & "=Data!" & g_strRangeSeries & Chr(34)
ActiveChart.SeriesCollection(1).XValues = strDataXValues

This correctly places
={"=Data!A2:A82"}
in
Source Data | Series | X Values:

The problem is with setting Y Values.

g_strRangeYVal = "B2:B82" (calculated in prior code)
strDataValues = Chr(34) & "=Data!" & g_strRangeYVal & Chr(34)
ActiveChart.SeriesCollection(1).Values = strDataValues

This incorrectly places
={0}
in
Source Data | Series | Y Values:

Can someone tell me why my method works for X, but not Y?

Thanks in advance for any info.

Larry Mehl

Reply to this Message

Jon Peltier - 01 Feb 2004 06:52 GMT

Larry -

> This correctly places
> ={"=Data!A2:A82"}
> in Source Data | Series | X Values:

This is actually placing a one-item array in the X Values box,
consisting of the string "=Data!A2:A82". Your Y values consist of a
single zero in brackets, because Excel had to coerce it to a number for
the Y values, and it always uses zero for text.

change your code to:

strDataXValues = "Data!" & g_strRangeSeries
ActiveChart.SeriesCollection(1).XValues = Range(strDataXValues)

or

ActiveChart.SeriesCollection(1).XValues = _
worksheets("Data").Range(g_strRangeSeries)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

> Hello --
>
[quoted text clipped - 25 lines]
>
> Larry Mehl

Reply to this Message

L Mehl - 01 Feb 2004 17:28 GMT

Hi Jon --

Thank you for explaining it, and for the suggestions.

Larry.

> Larry -
>
[quoted text clipped - 53 lines]
> >
> > Larry Mehl

Reply to this Message