Linear interpolation

[demian]

Hi, VBA Experts!

I'd like to ask for help:
I have to code the linear interpretation for missing data.
The way I did is pretty simple, but I got stuck somepoint..

As attached file, I have to find the missing data for the year 4 and 7 from the table(A1:B8) to be able to make the new data array(D1:E10)
I was bale to fill the missing year in the new table, but for the year to be interpolated with Zero value.
I need an advice regarding the logic for finding the next Filled Rate to interpolate with and the logic to be applied for the interpolation formula.

Any help is greatly appreciated.

Function Interpolate(oldRates As Variant) As Variant
Dim i As Integer, j As Integer, oldCount As Integer, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat As Variant
ReDim Mat(1 To Maturity, 1 To 2)   ' 1 to 10, 1 to 2:  the new array set up.
'Fills in the years
For i = 1 To Maturity
    Mat(i, 1) = i
    Mat(i, 2) = 0
Next i
'copies over known Rates.
For i = 1 To Maturity           ' 1 to 10: i
   For j = 1 To oldRates.Rows.Count    ' 1 to 8 : J
        If oldRates(j, 1) = Mat(i, 1) Then
            Mat(i, 2) = oldRates(j, 2)
        End If
   Next j
Next i
For i = 1 To Maturity
    If Mat(i, 2) = 0 Then
      'find the next Filled Rate to interpolate with
        For j = i + 1 To Maturity
           'if statement
        Next j
         ' Mat(i,2) = linear interpolation code here.
        Mat(i, 2) = Mat(i - 1, 2) + (Mat(i + 1, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
     End If
Next i
Interpolate = Mat
End Function