none
XY Scatter Plot problem RRS feed

  • Question

  • Hi all,

    using the following code I am trying to get an XY scatter plot from 2 columns of data.

    The problem with this approach is that it makes two series on the same plot plotting each column of data against row number. I want it to plot a single series, where y is the dependent variable and x is the independent variable. How do I do this?

    Sub ChartByCols()
        Dim myWorksheet As Worksheet
        Dim myChart As ChartObject
       
        Set myChart = ActiveSheet.ChartObjects.Add(100, 50, 500, 200)
       
        With myChart
            .Chart.SetSourceData Source:=Selection
            .Chart.ChartType = xlXYScatterSmoothNoMarkers
            .Chart.PlotBy = xlColumns
            .Chart.ApplyLayout (4)
            .Chart.ChartStyle = 1
        End With
       
    End Sub

    Wednesday, September 28, 2011 10:21 AM

Answers

  • In your macro change
    With myChart
     .Chart.SetSourceData Source:=Selection
     .Chart.ChartType = xlXYScatterSmoothNoMarkers

    to
    With myChart
     .Chart.ChartType = xlXYScatterSmoothNoMarkers
     .Chart.SetSourceData Source:=Selection

    Or try like this

    Sub test()
    Dim myWorksheet As Worksheet
    Dim myChart As ChartObject
    Dim sr As Series
            Set myChart = ActiveSheet.ChartObjects.Add(100, 50, 500, 200)
         Set sr = myChart.Chart.SeriesCollection.NewSeries
         sr.XValues = Selection.Columns(1)
         sr.Values = Selection.Columns(2)
         With myChart
                 .Chart.ChartType = xlXYScatterSmoothNoMarkers
                 .Chart.ApplyLayout (4)
                 .Chart.ChartStyle = 1
         End With
    End Sub

    Peter Thornton

    • Marked as answer by RichRout Wednesday, September 28, 2011 4:54 PM
    Wednesday, September 28, 2011 11:46 AM
    Moderator

All replies

  • In your macro change
    With myChart
     .Chart.SetSourceData Source:=Selection
     .Chart.ChartType = xlXYScatterSmoothNoMarkers

    to
    With myChart
     .Chart.ChartType = xlXYScatterSmoothNoMarkers
     .Chart.SetSourceData Source:=Selection

    Or try like this

    Sub test()
    Dim myWorksheet As Worksheet
    Dim myChart As ChartObject
    Dim sr As Series
            Set myChart = ActiveSheet.ChartObjects.Add(100, 50, 500, 200)
         Set sr = myChart.Chart.SeriesCollection.NewSeries
         sr.XValues = Selection.Columns(1)
         sr.Values = Selection.Columns(2)
         With myChart
                 .Chart.ChartType = xlXYScatterSmoothNoMarkers
                 .Chart.ApplyLayout (4)
                 .Chart.ChartStyle = 1
         End With
    End Sub

    Peter Thornton

    • Marked as answer by RichRout Wednesday, September 28, 2011 4:54 PM
    Wednesday, September 28, 2011 11:46 AM
    Moderator
  • Thanks Peter.

    Your second solution worked just fine!

    Keith

    Wednesday, September 28, 2011 4:55 PM