Horizontal interpolation for >1 empty cell

Hi

I'm a bit of an excel noob but getting better as time goes on.

I have a large spreadsheet and need to fill missing values with the average of the nearest neighbouring cell that has a value.

For example, some blank cells will have a neighbouring data point on each side, but some might have 2 or 3+ blank cells in a row before touching a cell with a datapoint. I want to fill all these cells just with a calculation that is also dependent on the number of cells blank, so it would need to change with the amount of blank cells.

eg
[A] [(A+B)/2] [B]
[A] [A + (B-A)(1/3)] [A + ((B-A)(2/3)] [B]


Thanks, any help is appreciated. I'll continue googling in the mean time.

Edit: It appears a stepwise fill command in excel can do this but only when I select the row manually. Can I apply this to the whole sheet?

Edit: It further appears that I need to do a linear interpolation for all missing data points, which can automatically find the closest horizontal data points and apply linear interpolation to the blanks in between, without modifying the existing data points. The biggest challenge seems to be that the guides on the internet use a strict reference point whereas I need a conditional reference which searches for the nearest data point from any given empty cell.

I have created the attached user defined function to interploate in columns

you might enjoy seeing if you can modify it to work on rows.

Public MyArray As Variant

Function Interpolate(Myrange As Range)
Interpolate = ""
If InStr(Myrange.Address, ":") = 0 Then GoTo skip2
Rangein = Split(Replace(Myrange.Address, ":", ""), "$")
If Rangein(2) = Rangein(4) Then GoTo skip2
If Myrange.Row = Application.Caller.Row Then
MyArray = Application.Transpose(Myrange.Value)

FillLoop:
For Pos = 1 To UBound(MyArray)
If MyArray(Pos) = "" Then Exit For
Next

If Pos >= UBound(MyArray) Then GoTo Skip1

For Pos2 = Pos + 1 To UBound(MyArray)
If MyArray(Pos2) <> "" Then Exit For
Next

If Pos2 = UBound(MyArray) And MyArray(Pos2) = "" Then GoTo Skip1

Variation = (MyArray(Pos2) - MyArray(Pos - 1)) / (Pos2 - Pos + 1)

For Fill = Pos To Pos2 - 1
MyArray(Fill) = MyArray(Fill - 1) + Variation
Next

GoTo FillLoop

Skip1:
Interpolate = MyArray(1)
Else
Interpolate = MyArray(Application.Caller.Row - Myrange.Row + 1)
End If
skip2:
'Exit Function
End Function

Public MyArray As Variant

Function HInterpolate(Myrange As Range)
HInterpolate = ""
If InStr(Myrange.Address, ":") = 0 Then GoTo skip2
Rangein = Split(Replace(Myrange.Address, ":", ""), "$")
If Rangein(1) = Rangein(3) Then GoTo skip2
If Myrange.Column = Application.Caller.Column Then
MyArray = Application.Transpose(Application.Transpose(Myrange.Value))

FillLoop:
For Pos = 1 To UBound(MyArray)
If MyArray(Pos) = "" Then Exit For
Next

If Pos >= UBound(MyArray) Then GoTo Skip1

For Pos2 = Pos + 1 To UBound(MyArray)
If MyArray(Pos2) <> "" Then Exit For
Next

If Pos2 = UBound(MyArray) And MyArray(Pos2) = "" Then GoTo Skip1

Variation = (MyArray(Pos2) - MyArray(Pos - 1)) / (Pos2 - Pos + 1)

For Fill = Pos To Pos2 - 1
MyArray(Fill) = MyArray(Fill - 1) + Variation
Next

GoTo FillLoop

Skip1:
HInterpolate = MyArray(1)
Else
HInterpolate = MyArray(Application.Caller.Column - Myrange.Column + 1)
End If
skip2:
'Exit Function
End Function

Function Interpolate(Myrange As Range)
Interpolate = ""
If InStr(Myrange.Address, ":") = 0 Then GoTo skip2
Rangein = Split(Replace(Myrange.Address, ":", ""), "$")
If Rangein(2) = Rangein(4) Then GoTo skip2
If Myrange.Row = Application.Caller.Row Then
MyArray = Application.Transpose(Myrange.Value)

FillLoop:
For Pos = 1 To UBound(MyArray)
If MyArray(Pos) = "" Then Exit For
Next

If Pos >= UBound(MyArray) Then GoTo Skip1

For Pos2 = Pos + 1 To UBound(MyArray)
If MyArray(Pos2) <> "" Then Exit For
Next

If Pos2 = UBound(MyArray) And MyArray(Pos2) = "" Then GoTo Skip1

Variation = (MyArray(Pos2) - MyArray(Pos - 1)) / (Pos2 - Pos + 1)

For Fill = Pos To Pos2 - 1
MyArray(Fill) = MyArray(Fill - 1) + Variation
Next

GoTo FillLoop

Skip1:
Interpolate = MyArray(1)
Else
Interpolate = MyArray(Application.Caller.Row)
End If
skip2:
'Exit Function
End Function