Setting the Values Property of the Series Class

[Alex A]

This has stumped me for three days now. I need to be
able to dynamically set the XValues and the (Y)Values for
a graph through VBA. I am getting a run -time error 1004
stating it is unable to set the XValues Property of the
Series Class. ANYONE PLEEZ! I NEED KUNG FOO LESSONS
FROM THE MASTER...

Compiler takes me to this line when it errors...
ActiveChart.SeriesCollection(1).XValues = XValues

Which is related to this line...
XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1"

There must be a better way to set the SeriesCollection
(1).XValues, but a macro uses this =Query! statement but
it only seems to work for static numbers and I have tried
to slip in my variable 'XVALUES'?????????????????
HELP!!!!


HERE IS THE WHOLE RELEVANT BLOCK OF CODE:
x1 = IVSLH2Range.Row + 1

If IVSLH2Range.Offset(intRowCountIVSLH2 - 1, 2).Value
< Year(Date) Then
x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 1
Else
x2 = IVSLH2Range.Row + intRowCountIVSLH2 - 2
End If

'Update Graph Variables
intRowCountIVSLH2 > 2 Then
XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1"
YValues = "=Query!R" & CStr(x1) & "C5:R" & CStr(x2) & "C5"

'Update Graph2
Sheets("Chart2").Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = XValues
ActiveChart.SeriesCollection(1).Values = YValues
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = strCompanyName
End With
ActiveChart.ChartArea.Select

 

[Brad]

Build your dynamic range by using a range object.

Something like this:

Dim XValues As Range
x1 = 2
x2 = 6
With ActiveSheet
Set XValues = .Range(.Cells(x1, 2).Address, .Cells
(x2, 2).Address)
End With
ActiveChart.SeriesCollection(1).XValues = XValues

I don't think you can use Excel formulas as VBA Object
properties. When you enter the formula in Excel, I
believe it converts it to a range. Either way, that code
should work.

HTH.
-Brad

 

[Jon Peltier]

Check out the response in the charting group.

- Jon