Problem programming .SeriesCollection().XValues and .Values

[Greg Fish]

Hi everyone. I am having a difficult time programmatically assigning a named
range using the method SeriesCollection().XValue and SeriesCollection().Value.

I keep getting the following error message, "Run time error '1004' Unable to
set the Values property of the Series class. I am fairly novice VBA
programmer, mostly self taught, I have learned signigicantly over the last
few months but cannot overcome this one error. I have tried to emulate the
code written by Jon Peltier in his VBA and Chart primer, from his website.

my code is below,

Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As
String, curve As String)
Dim objWorkSheet As Object
Dim Y_data_range As String
Dim X_data_range As String
Dim Y_est_data_range As String
Dim X_est_data_range As String
Dim fittedCurve As String
Dim fuelCurve As ChartObject

Set Y_data_range = OLEObject.Sheet(Sheet2).Range(y1Axis)
X_data_range = x1Axis
Y_est_data_range = y_EST
X_est_data_range = x_EST
fittedCurve = curve

Set fuelCurve = ActiveSheet.ChartObjects.Add _
(Left:=500, Width:=750, Top:=150, Height:=450)
With fuelCurve.Chart

'make smoth XY Scatter Chart
.chartType = xlXYScatterSmooth
'
' add series from selected name ranges
.SeriesCollection(1).Name = "Data" (ERROR occurs here)
.SeriesCollection(1).XValues = "=(Sheet2!)" & X_data_range
.SeriesCollection(1).Values = "=" (Sheet2!)" & Y_data_range
.SeriesCollection(2).Name = "Estimated"
.SeriesCollection(2).XValues = "=(Sheet2!)" & X_est_data_range
.SeriesCollection(2).Values = "=(Sheet2!)" & Y_est_data_range
End With
End Sub


Regards,
Greg

 

[Peter T]

'make smoth XY Scatter Chart

.chartType = xlXYScatterSmooth
'
' add series from selected name ranges
.SeriesCollection(1).Name = "Data" (ERROR occurs here)

At this point your chart doesn't have any series, try
With .SeriesCollection.NewSeries
.Name = "Data"
' etc
end with

And similar for subsequent series. Or,
Dim sr as Series
' code
Set sr =.SeriesCollection.NewSeries
and work with 'sr'

In passing, this can't be right -

Dim Y_data_range As String
Set Y_data_range = OLEObject.Sheet(Sheet2).Range(y1Axis)

Regards,
Peter T

 

[Greg Fish]

Peter T -

I followed your suggestion as shown

Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As
String, curve As String)

Dim Y_data_range As String
Dim X_data_range As String
Dim Y_est_data_range As String
Dim X_est_data_range As String
Dim fittedCurve As String
Dim fuelCurve As ChartObject

Y_data_range = y1Axis
X_data_range = x1Axis
Y_est_data_range = y_EST
X_est_data_range = x_EST
fittedCurve = curve

Set fuelCurve = ActiveSheet.ChartObjects.Add _
(Left:=500, Width:=750, Top:=150, Height:=450)
With fuelCurve.Chart

'make smoth XY Scatter Chart
.chartType = xlXYScatterSmooth
'
' add series from selected name ranges
With .SeriesCollection.NewSeries
.Name = "Data"
.XValues = "=" & X_data_range

LIND OF CODE ABOVE TRIGGERS RUN TIME ERROR UNABLE TO SET THE XVALUES
PROPERTY OF THE SERIES CLASS (note used all CAPS to distinguish text)

.Values = "=" & Y_data_range
End With
With .SeriesCollection.NewSeries
.Name = "Estimated"
.XValues = "=" & X_est_data_range
.Values = "=" & Y_est_data_range
End With
End With
End Sub

With recommended changes still getting an error, that I was having before.
I have a named range, which I am basically passing as a string argument to
the function, this seems pretty straightforward, since the string should just
serve as an argument for the function call, in this case XValues. Thanks for
your help.

Greg

 

[Greg Fish]

After taking in the advice and pouring over this forum, not to mention
several Google searches have rewritten the Sub routine as follows:

Sub graphIt(x1Axis As String, y1Axis As String, x_EST As String, y_EST As
String, curve As String)
Dim objWorkSheet As Object
Dim Y_data_range, X_data_range As Range
Dim Y_est_data_range, X_est_data_range As Range
Dim sY_data_range As String
Dim sX_data_range As String
Dim sY_est_data_range As String
Dim sX_est_data_range As String
Dim fittedCurve As String
Dim fuelCurve As ChartObject

Worksheets(2).Activate
Set Y_data_range = ActiveSheet.Range(y1Axis)
Set X_data_range = ActiveSheet.Range(x1Axis)
Set Y_est_data_range = ActiveSheet.Range(y_EST)
Set X_est_data_range = ActiveSheet.Range(x_EST)
sY_data_range = "'" & ActiveSheet.Name & "'!" & Y_data_range.Address
sX_data_range = "'" & ActiveSheet.Name & "'!" & X_data_range.Address
sY_est_data_range = "'" & ActiveSheet.Name & "'!" &
Y_est_data_range.Address
sX_est_data_range = "'" & ActiveSheet.Name & "'!" &
X_est_data_range.Address
fittedCurve = curve

Worksheets(1).Activate
Set fuelCurve = ActiveSheet.ChartObjects.Add _
(Left:=500, Width:=750, Top:=150, Height:=450)
With fuelCurve.Chart

'make smoth XY Scatter Chart
.chartType = xlXYScatterSmooth
'
' add series from selected name ranges
With .SeriesCollection.NewSeries

.XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT
.Values = "=(" & sY_data_range & ")"
.Name = "Data"
End With
With .SeriesCollection.NewSeries
.Name = "Estimated"
.XValues = "=" & X_est_data_range
.Values = "=" & Y_est_data_range
End With
End With
End Sub

The annoying part is, still getting the same error message. Absolutely
frustrating especially when I KNOW that the value for sX_data_range is in
fact the correct data, and address for the named range. This could not be
anymore frustrating, not to mention having a pending deadline.

 

[Peter T]

from your slightly earlier post

.XValues = "=" & X_data_range

LIND OF CODE ABOVE TRIGGERS RUN TIME ERROR UNABLE TO SET THE
XVALUES PROPERTY OF THE SERIES CLASS

and below

.XValues = "=(" & sX_data_range & ")" STILL ERRORING OUT

You don't want those brackets but -

Change the range address to R1C1 style, eg

s = "='" & ActiveWorkbook.Name & "'!"
s = s & myRange.Address(ReferenceStyle:=xlR1C1) ' or .Address(, , xlR1C1)
mySeries.Values = s ' or .XValues = s

Regards,
Peter T

 

[Greg Fish]

Okay I am confused now, so what is the point of using the named range, if I
am just going to end up putting in the R1C1 style reference? This just seems
inordinately difficult.

I am not sure if I follow your syntax
s = s & myRange.Address(ReferenceStyle:=xlR1C1) ' or .Address(, , xlR1C1)
mySeries.Values = s ' or .XValues = s

Forgive my exasperation, but this has been far more difficult than I ever
imagined, and I am rapidly running out of time.

 

[Greg Fish]

Peter T -

First off I want to thank you mightily, you pointed me in the right
direction, I changed part of the code to the following, and now it works

Set Y_data_range = ActiveSheet.Range(y1Axis)
Set X_data_range = ActiveSheet.Range(x1Axis)
Set Y_est_data_range = ActiveSheet.Range(y_EST)
Set X_est_data_range = ActiveSheet.Range(x_EST)
sY_data_range = "'" & ActiveSheet.Name & "'!" &
Y_data_range.Address(ReferenceStyle:=xlR1C1)
sX_data_range = "'" & ActiveSheet.Name & "'!" &
X_data_range.Address(ReferenceStyle:=xlR1C1)
sY_est_data_range = "'" & ActiveSheet.Name & "'!" &
Y_est_data_range.Address(ReferenceStyle:=xlR1C1)
sX_est_data_range = "'" & ActiveSheet.Name & "'!" &
X_est_data_range.Address(ReferenceStyle:=xlR1C1)

I cannot begin to thank you enough. That was extremely bedeviling error to
get through.

 

[Peter T]

I'm confused too, your code appeared to be applying an address not a name,
hence my example was loosly based on correcting (way below) -

This should work with for a named range -

s = "='" & ActiveWorkbook.Name & "'!" & "myName
mySeries.Values = s ' or .XValues = s

I confess though I may not have read your code fully.
However in your slightly later post all appears to be working, great <g>

Regards,
Peter T