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

 

[Jon Peltier]

"Query" in the faulty line is not a statement, it is the worksheet name
containing the range you want to apply to the series values. If that's
not the worksheet name, you need to change it. The worksheet name needs
to be in single quotes inside the double quotes if it contains a space.

Otherwise your code works fine. I just ran this little snippet with no
problem, even if the new chart data range was blank:

Sub ChangeXYValues()
Dim x1 As Long, x2 As Long
Dim myXValues As String
Dim myYValues As String
x1 = 9
x2 = 12
myXValues = "=Sheet1!R" & CStr(x1) & "C4:R" & CStr(x2) & "C4"
myYValues = "=Sheet1!R" & CStr(x1) & "C5:R" & CStr(x2) & "C5"
With ActiveChart.SeriesCollection(1)
.XValues = myXValues
.Values = myYValues
End With
End Sub

- Jon

 

[Guest]

Jon, how's it going? Hey, I checked my sheet name and it
is in fact "Query". Your code is pretty much exactly the
same as mine and it does work the first time and as many
times after that as long as the x1 and x2 values don't
change. When my spreadsheet changes, the x1 and x2
values change and that is when I get the error. Can you
figure out why? It boggles my mind. Do I have to set
the Xvalues Property to Nothing before I can re assign
them?

Alex A.
DBA

 

[Alex A]

I just realized that it works for certain customer
numbers even when the spreadsheet changes and the... but
then all of a sudden it will fail. It's a good clue that
somehow it refuses to work for certain exceptions but I
can't determine what it is yet. Possibly if there are
too few amount of rows of data. I built an If structure
to weed out less than 2 rows.

AA

 

[Jon Peltier]

Alex -

Check that you know where the x1 and x2 values are coming from and how
they are derived.

- Jon

 

[sig]

I think I have a similar, if not the exact same problem. Has anyon
found a fix or work around?

Thanks

 

[Jon Peltier]

Sig -

The original message was cut out. Repost your problem statement, and I'm
sure someone will try to help.

- Jon