Series data for scatter plot in VBA

[tja26]

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

[Andy Pope]

Hi Tim,

Try this code. The problem arises when the first item in the data series is blank or NA().
So this replaces the first pair of values and plots the series.
Then it replaces the orginial values.

Sub ChartNAData() 
    Dim rngXs As Range 
    Dim rngYs As Range 
    Dim vntTempX As Variant 
    Dim vntTempY As Variant 
     
    Set rngXs = Range("B3:B13") ' X values
    Set rngYs = Range("C3:C15") ' Y values
     
     ' store and replace first values
    vntTempX = rngXs.Cells(1, 1).Formula 
    rngXs.Cells(1, 1) = 1 
     
    vntTempY = rngYs.Cells(1, 1).Formula 
    rngYs.Cells(1, 1) = 1 
     
    With ActiveChart 
        .SeriesCollection(1).XValues = rngXs 
        .SeriesCollection(1).Values = rngYs 
         
         ' replace first values
        rngXs.Cells(1, 1).Formula = vntTempX 
        rngYs.Cells(1, 1).Formula = vntTempY 
    End With 
     
End Sub 

I did a little testing including setting all data values to NA().

I would be interested to know if this code works on your charts.

[tja26]

Genius!

It's worked like a dream. I suspected the problem might be something to do with where the NA's fell but had no idea how to solve it. Thanks so much. These scatter plots seem to be ridiculously difficult to write code for...



Thanks again

Tim