Assuming you have at least two series (you'd get a subscript out of range
error if that weren't the case) and myLeftsideRange is a valid range, what
could be causing the error is in not having a valid range for the series X
and Y values before running this code.
Marker series (XY and Line) choke on this, but if you temporarily change to
a fill series (column or area), you can access the series data even if the
data was not valid to begin with.
The other problem would be having a discontiguous range that contains more
areas than the series formula has characters to define their addresses. In
other words, if I define a contiguous range, it looks like
Sheet1!$A$1:$A$10
in the series formula. If I select the ten cells individually, this becomes
(Sheet1!$A$1,Sheet1!$A$2,Sheet1!$A$3,Sheet1!$A$4,S heet1!$A$5,Sheet1!$A$6,Sheet1!$A$7,Sheet1!$A$8,She et1!$A$9,Sheet1!$A$10)
At some point, this string becomes too long, and Excel chokes on the
formula.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
wrote in message
ps.com...
I'm creating an app that creates a scatter plot based on two columns
of data in the spreadsheet. It breaks and throws a "Run-time error
1004 - unable to set the XValues property of the series class" under
very specific circumstances. The app allows one to click data point
labels on a chart to include/exclude them from the plot. The data
points are toggled into and out of the plot this way. Here is the
relevant code:
========================
Function RebuildSeries_2() As Integer
Dim tempInt As Integer, tempRange As Range
Set myRange = BuildMyRange(DataAreaLeft, DataAreaRight,
DataAreaTop, DataAreaBottom, nDataPoints, DataPointArray)
ActiveChart.SeriesCollection(2).XValues = myLeftsideRange '
breaks on this line
ActiveChart.SeriesCollection(2).Values = myRightsideRange ' also
breaks on this line
End Function
========================
It breaks on the
ActiveChart.SeriesCollection(2).XValues = myLeftsideRange '
breaks on this line
line.
The "BuildMyRange" function uses a lookup table to build ranges for
the scatterplot. E.g., it will add in all the desired cells in one
column into the range "myLeftsideRange". Does the same for the right
side range. The code for that function is at the end of this message.
This works perfectly well in Excel 2007, but fails in Excel 2000 if
the range created is disjoint (e.g., if there is a gap in the column
of cells used to create the range). The range builder always builds up
the range from individual cells, so I don't think the problem is
having a range built up that way.
It works correctly in Excel 2000 so long as the created range is
contiguous. I need to get it working for both Excel 2000 and Excel
2007.
Thanks for any help.
Larry Neer
========================
Function BuildMyRange(DataAreaLeft As Integer, DataAreaRight As
Integer, DataAreaTop As Integer, _
DataAreaBottom As Integer, nDataPoints As Integer,
DataPointArray() As Boolean) As Range
Dim i As Integer, tempRange As Range, SFlag As Boolean
' Excel barfs if I try to build a range starting with a null
(nothing) range.
' use a Flag here to get around it (flag is false until find
the first included range)
SFlag = False
For i = 1 To nDataPoints
If (DataPointArray(i)) Then
If (Not SFlag) Then
SFlag = True
Set BuildMyRange = Range(ActiveSheet.Cells(i +
DataAreaTop - 1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaRight))
Set myLeftsideRange = Range(ActiveSheet.Cells(i +
DataAreaTop - 1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaLeft))
Set myRightsideRange = Range(ActiveSheet.Cells(i +
DataAreaTop - 1, DataAreaRight), ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaRight))
End If
Set tempRange = Range(ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaRight))
Set BuildMyRange = Union(BuildMyRange, tempRange)
Set tempRange = Range(ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaLeft), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaLeft))
Set myLeftsideRange = Union(myLeftsideRange, tempRange)
Set tempRange = Range(ActiveSheet.Cells(i + DataAreaTop -
1, DataAreaRight), ActiveSheet.Cells(i + DataAreaTop - 1,
DataAreaRight))
Set myRightsideRange = Union(myRightsideRange, tempRange)
End If
Next i
End Function
========================