xyPlot automated series addition

[PossiblyNot]

Hi all,

I'm currently making an excel workbook where a user inputs a range of data which gets processed and reorganised into a table like the one below found in the "Intermediate" sheet in the workbook. There can be anything from 1 series to dozens, even hundreds.

Name	Y1	Y2	X1	X2
Test1	21	19	1	1
Test2	11.6	5.4	2	2
Test3	2.8	2.6	3	3
Test4	28.4	11.6	4	4
Test5	65.4	23	5	5

<tbody>
</tbody>

I'm hoping to plot a simple scatter plot to plot vertical ranges (y1 and y2) using VBA to automate the entire process

my current stab at the code as follows

Sub BioStratPlotter()

Charts.Add


ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SeriesCollection.NewSeries
    
Dim i As Variant


Set i.Value = 3
  
  Do While i.Value <> ""
 
 For i = 3 To 48
     
   'each row is a different series, with each row as such: [Name], [Y value], [Y value], [X value], [X value]
   ActiveChart.SeriesCollection(6).XValues = "=Intermediate!R(i)$C5:IntermediateR(i)$C6"
    ActiveChart.SeriesCollection(6).Values = "=Intermediate!R(i)$C3:IntermediateR(i)$C4"
    ActiveChart.SeriesCollection(6).Name = "=Intermediate!R(i)$C2"
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="BioStrat"
    'Options for the graph
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    With ActiveChart.Axes(xlValue)
        'Scale is from 0Mya to 65.4 Mya (End-Cretaceous)
        .MinimumScale = 0
        .MaximumScale = 65.4
        .MinorUnitIsAuto = True
        .MajorUnit = 10
        .Crosses = xlAutomatic
        'X-axis is plotting downwards
        .ReversePlotOrder = True
        .ScaleType = xlAutomatic
        .DisplayUnit = xlNone
    End With


 'loop statement clicks down one row
Next i


Loop
            
End Sub

the current error is a 424 error (object required) however, I sense my logic is flawed regardless.

What have I royally fudged?

Cheers

PossiblyNot