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
========================