Excel VBAで補間

前方にも後方にも線形補間を適応したかった。
PythonのPandasを使って思うようにできなかったのでExcelを用いることにした。
以下は個人的メモです。

Sub 補間()
    
    Dim count_ As Integer
    
    count_ = Selection.count
    
    Selection.Item(1).Activate
    
    Dim a As Integer
    Dim b As Integer
    
    Dim val1 As Double
    Dim val2 As Double
    
    Dim i As Integer
    
    For i = 0 To count_ - 1 Step 1
        If Not ActiveCell.Offset(i, 0).Formula = "" Then
            a = i + 1
            val1 = ActiveCell.Offset(i, 0).Value
            Exit For
        End If
    Next
    
    For i = count_ - 1 To 0 Step -1
        If Not ActiveCell.Offset(i, 0).Formula = "" Then
            b = i + 1
            val2 = ActiveCell.Offset(i, 0).Value
            Exit For
        End If
    Next
    
    Dim x As Double
    Dim y As Double
    
    x = (val1 - val2) / (a - b)
    y = (val2 * a - val1 * b) / (a - b)
    
    Dim result As Double
    
    For i = 0 To count_ - 1 Step 1
        If ActiveCell.Offset(i, 0).Formula = "" Then
            result = (i + 1) * x + y
            ActiveCell.Offset(i, 0).Value = Switch(result < 0, 0, True, result)
        End If
    Next
    
End Sub