Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: Series data for scatter plot in VBA

  1. #1
    Join Date
    11th October 2004

    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 &
    .XValues = dataSheet1.Range(col2 & "2:" & col2 &
    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

    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

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    7th March 2003
    Essex, England
    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.


  3. #3
    Join Date
    11th October 2004

    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


    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 9
    Last Post: February 5th, 2009, 01:59
  2. Data Labels Points in Scatter Plot
    By CoolBuddy in forum Excel General
    Replies: 7
    Last Post: June 19th, 2008, 19:29
  3. Plot Each Selected Row Range As Series In Scatter Chart
    By cricks11 in forum Excel General
    Replies: 10
    Last Post: May 17th, 2008, 18:06
  4. Scatter Plot Multiple Series
    By thampw in forum Excel General
    Replies: 1
    Last Post: September 13th, 2007, 12:43
  5. Unable to set series on a Scatter Plot
    By mtaylor1983 in forum Excel General
    Replies: 6
    Last Post: November 3rd, 2005, 03:58


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts