LinearInterpolationFormulas

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


• NA block (coordinate) is the series of consecutive NA errors and the adjacent top and bottom numeric values. Rows 6 to 8 in figure 1
• Line 21: Top (coordinate) is the top numeric value with NA offset +1 row. Cell C6 with value 20 in figure 1
• Line 24: Bottom (coordinate) is the (bot)tom numeric value with NA offset -1 row. Cell C8 with value 40 in figure 1
• Using row 7 (cell C7) as an example, the values of Top, Bot, and Run are (figure 2): Fig 2: Watches Window - interpolation variables Top, Bot, and Run
• Line 29: FormulaR1C1 "=R[-1]C+(R" & Bot & "C-R" & Top & "C)/" & Run, returns
"=R[-1]C+(R8C-R6C)/2", equivalent to
=C6+(C$8-C$6)/2
• Line 30 adds the Yellow Fill (VBA Interior property) to each cell containing the interpolation formula

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()