Chart series, set start/end dynamically

[Schadenfreud]

The formula used to create the marked series line.

=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:$A$122;Rolling_window_data!$B$3:$B$122;1)

The statistics in Rolling_window_data.

I need the marked line to be different from/to a point in time (x scale), depending on the backfill date in Rolling_window_data. All the contents are generated programmatically so the backfill date cell with a value will almost always be different. From what I've researched, I should make 2 series that overlap each other (in the current case, the 1st series should end on line 27 and the second should start from line 27 and continue to the end). The start is always line 3 and the end is always line 122.

How do I dynamically set the series start/end so I can make them overlap each other? I'm assuming I should add some kind of 'IF Statement' to the series code but I'm very new to excel formulas and I've no idea how to make it. I was thinking something in the lines of:

=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:IF(FOR Each Cell in Range("D3:D122") cell.value <> #N/A, Cell, "D122");Rolling_window_data!$B$3:$B$122;1)

 

[Schadenfreud]

Another approach I thought of was using an advanced filter for the values in range D3:D122 which looks for something that does not match #N/A, then I need to set the result of that filtering as the end/start points of the SERIES, but again I'm not certain how to write that so it happens automatically.

 

[Schadenfreud]

I've let go of the idea of doing it with a formula and jumped to a VBA solution, but I can't seem to be able to compare the cell value to #N/A. I get a 'Run time Error(13): Type mismatch', using the following code:

 Sub backfillDataSeries()    'make new series in Port Historical Risk
    'if there is a backfill date in Rolling window data
Dim na As String
na = "#N/A"


For i = 3 To 122
    If (Cells(i, 4).Value <> na) Then
        With Sheet23.ChartObjects("Chart 4").SeriesCollection(1)
            .Values = "=Rolling_window_data!$B$3:$B$" & i
            .XValues = "=Rolling_window_data!$A$3:$A$" & i
        End With
        With Sheet23.ChartObjects("Chart 4").SeriesCollection.NewSeries
            .Values = "=Rolling_window_data!$B$" & i & ":$B$122"
            .XValues = "=Rolling_window_data!$A$" & i & ":$A$122"
            .Name = "Backfilled"
            .Format.Line.DashStyle = msoLineDash
        End With
    End If
Next i


End Sub

Any idea what i should do to make this work?

P.S. I tried StrComp but it also didn't work properly.