Announcement

Collapse
No announcement yet.

Series data for scatter plot in VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Series data for scatter plot 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

  • #2
    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.

    Code:
    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.

    Cheers
    Andy

    Comment


    • #3
      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

      Comment

      Working...
      X