Can not set Y Values in XY chart

  • Thread starter Thread starter L Mehl
  • Start date Start date
L

L Mehl

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
 
Larry -
This correctly places
={"=Data!A2:A82"}
in Source Data | Series | X Values:
Click to expand...

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
 
Back
Top