How to set series data in VBA
 

I have been struggling for a long time trying to find a
robust way of being able to set the range of values for a
scatter plot series. I have found a variety of answers
on the web - it seems to be a common problem. The code
that I found worked best for me was this:

With ActiveChart.SeriesCollection(6)
.Values = dataSheet1.Range(col1 & "2:" & col1 &
numRows1)
.XValues = dataSheet1.Range(col2 & "2:" & col2 &
numRows1)
End With

This had been working perfectly for six or seven scatter
plots within the workbook. I have recently added another
scatter plot using the same code. It works most of the
time, however, for certain data selections it comes up
with the same error that I have been battling with for
ages:

Run-time error '1004'

Unable to set the XValues property of the Series class

The data range that I am trying to set it to consists of
either numbers or NA(). When there is a bug, if I debug
and then go back to the chart and manually set the data
then there is no problem and the chart is correct and the
macro then proceeds without fault.

I don't understand why it works in most cases. Is there
a robust method of setting series data for scatter plots
(all my histograms work with no problems)?

Any help is much appreciated,

Tim Archer