Append data series for scatter plot

Question

I am trying to create a scatter plot with only ONE series whose value is from multiple rows. In my code, I am appending the data from each row but I am getting an error. I appreciate any kind of support.

Private Sub generate_scatterplot()
    Dim oChartObj As ChartObject
    Dim oChart As Chart
    Dim rSourceData As Range

    Set oChartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)
    Set oChart = oChartObj.Chart

    Set rSourceDataX = Range("A2:C5")
    Set rSourceDataY = Range("D2:F5")
    
    With oChart
        .ChartType = xlXYScatter
        Set s = .SeriesCollection.NewSeries
        s.XValues = ""
        s.Values = ""
        For i = 1 To rSourceDataX.Rows.Count
                s.XValues = "=" & s.XValues & rSourceDataX.Rows(i)
                s.Values = "=" & s.Values & rSourceDataY.Rows(i)
        Next i     
    End With
End Sub

Error: Type mismatch (first line inside For loop)

Answer 1

Something like this:

Sub generate_scatterplot()
    Dim rSourceDataX As Range, rSourceDataY As Range
    Set rSourceDataX = Range("A2:C5")
    Set rSourceDataY = Range("D2:F5")
    Dim aSourceDataX, aSourceDataY, v, i As Long
    
    ReDim aSourceDataX(1 To rSourceDataX.Count) As Double
    ReDim aSourceDataY(1 To rSourceDataY.Count) As Double
    i = 0
    For Each v In rSourceDataX
        i = i + 1
        aSourceDataX(i) = v
    Next v
    i = 0
    For Each v In rSourceDataY
        i = i + 1
        aSourceDataY(i) = v
    Next v
    
    Dim oChartObj As ChartObject
    Set oChartObj = ActiveSheet.ChartObjects.Add(Top:=10, Left:=325, Width:=600, Height:=300)

    With oChartObj.Chart
        .ChartType = xlXYScatter
        With .SeriesCollection.NewSeries
           .XValues = aSourceDataX
           .Values = aSourceDataY
        End With
    End With
    
End Sub