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.

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