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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Y1[/TD]
[TD]Y2[/TD]
[TD]X1[/TD]
[TD]X2[/TD]
[/TR]
[TR]
[TD]Test1[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Test2[/TD]
[TD]11.6[/TD]
[TD]5.4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Test3[/TD]
[TD]2.8[/TD]
[TD]2.6[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Test4[/TD]
[TD]28.4[/TD]
[TD]11.6[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Test5[/TD]
[TD]65.4[/TD]
[TD]23[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


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