Linear interpolation with FormulaR1C1

Linear interpolation - example

The objective is to replace #N/A errors in a data vector (figure 1 top panel) with formulas returning the interpolated value (figure 1 bottom panel).

Fig 1: Interpolation example - sample NA vector in column C (top panel), and interpolated value, with A1 style in FormulaBar and R1C1 returned by the Excel FORMULATEXT function (bottom panel)

The VBA code is provided in the next section.

Linear interpolation - the VBA code

Code 1: Macro InterpFormulas replaces NA errors with interpolation formulas
Sub InterpFormulas()
' Type declaration characters (TDC): % Integer; & Long
Dim Top&, Bot&
Dim Run As Double
Dim i%, j%, k%
Dim DataPoints&, RefRow&, LastRow&
Dim Ref As Range
Dim Wsf As WorksheetFunction

Set Wsf = WorksheetFunction
Set Ref = Range("Anchor").Offset(0, 1)

' Coordinates
LastRow = Ref.End(xlDown).Row
RefRow = Ref.Row    ' Anchor row
DataPoints = LastRow - RefRow

    ' Identify NA block
    For i = 0 To DataPoints
        If IsNumeric(Ref.Offset(i, 0)) And Wsf.IsNA(Ref.Offset(i + 1, 0)) Then
            Top = Ref.Offset(i, 0).Row
            j = i
        ElseIf IsNumeric(Ref.Offset(i + 1, 0)) And Wsf.IsNA(Ref.Offset(i, 0)) Then
            Bot = Ref.Offset(i, 0).Row + 1
            Run = Bot - Top

    ' Write formulas
                For k = 1 To Run - 1
                    Ref.Offset(k + j, 0).FormulaR1C1 = "=R[-1]C+(R" & Bot & "C-R" & Top & "C)/" & Run
                    Ref.Offset(k + j, 0).Interior.ColorIndex = 6
                Next k
        End If
    Next i

End Sub

About code 1 (terminology):

Fig 2: Watches Window - interpolation variables Top, Bot, and Run

The Reset procedure

Code 2 resets the Data1 range (Range("Anchor").Offset(0, 1)) by copying the replacement data from the TestData range. The Paste argument (line 46) overwrites the Yellow Fill color from code 1 line 30.

Code 2: Macro ResetData overwrites the interpolated values and clears the yellow format
Sub ResetData()
Dim Addr As String

    Addr = ActiveCell.Address

    Range("Anchor").Offset(0, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Application.CutCopyMode = False


End Sub