Using VBA Excel to create Scatter Plot

Question

This is my VBA code to create a scatter plot in Excel:

Sub createmychart()
    Dim Chart1 As Chart
    Set Chart1 = Charts.Add
    With Chart1
        .SetSourceData Source:=Sheets("usd_download data").Range("A2:B26001")
        .ChartType = xlXYScatter
    End With
End Sub

However, it ended up like this:

But I would like to have a graph like this:

I tried a lots but cannot figure out how to do this.

Giana

I wonder if putting .ChartType before .SetSourceData might help Excel parse the data into X and Y columns. – Flopeared

Answer 1

Without knowing what your data looks like, your code appears to have two series of data while you want just one.

Sub createmychart()
    Dim Chart1 As Chart
    Set Chart1 = Charts.Add
    With Chart1
        .ChartType = xlXYScatter
        .SeriesCollection.NewSeries
        'Change to what your series should be called
        .SeriesCollection(1).Name = "=""Values"""
        .SeriesCollection(1).XValues = "=usd_download data!$A$2:$A$26001"
        .SeriesCollection(1).Values = "=usd_download data!$B$2:$B$26001"
    End With
End Sub

Brittabrittain

Hi Cronos, thanks for your comment. the data just simply has 2 columns, 1st column is USD, with numbers from A2 to A26001, and the 2nd column is Downloads, with numbers from B2 to B26001. Can I ask what is "NewSeries"? – Giana

Answer 2

I was struggling with the chartobject, chart and SeriesCollection as well. NewSeries is the method to add one Series in the SeriesCollection collection, and since it's only one series, so you can use SeriesCollection(1) to refer to it.

Meaning

Answer 3

This worked for me (sorry different cell ranges but u get the idea)

Private Sub CommandButton1_Click()
Dim xrng As Range
Dim yrng As Range


Set xrng = Range("A12:A17")
Set yrng = Range("B12:B17")

Dim Chart1 As Chart
    Set Chart1 = Charts.Add

 With Chart1
        .ChartType = xlXYScatter
        .SeriesCollection.NewSeries
        'Change to what your series should be called
        .SeriesCollection(1).Name = "=""Values"""
        .SeriesCollection(1).XValues = xrng
        .SeriesCollection(1).Values = yrng

    End With

End Sub

Pliable

Answer 4

If the scatter plot x values contain empty cells then excel replaces the x values with a list of 1 .. Npoints. The solution in this case is to limit the input range only to cells which contain numeric values (i.e. make sure no empty cells are included).

Proper