Option Explicit
'
Sub InterpolerColonnes()
Dim Cel As Range
For Each Cel In Feuil1.[A4:E4]
InterpolerNonNum Cel
Next Cel
End Sub
'
Sub InterpolerNonNum(ByVal Cel As Range)
Dim CelDéb As Range, CelFin As Range, Cel As Range, NbL As Long
Application.Calculation = xlCalculationManual
Do Until IsEmpty(Cel.Value)
If Not IsNumeric(Cel.Value) Then
If NbL = 0 Then Set CelDéb = Cel
NbL = NbL + 1
ElseIf NbL > 0 Then
PoserInterm CelDéb.Resize(NbL)
NbL = 0
End If
Set Cel = Cel.Offset(1)
Loop
If NbL > 0 Then PoserInterm CelDéb.Resize(NbL)
Application.Calculation = xlCalculationAutomatic
End Sub
'
Sub PoserInterm(Optional ByVal Plage As Range)
If Plage Is Nothing Then Set Plage = Selection
Dim NbL As Long, Z As String
NbL = Plage.Rows.Count: Z = "R[" & NbL & "]C"
Plage.Rows(1).FormulaR1C1 = "=R[-1]C+(" & Z & "-R[-1]C)/ROWS(RC:" & Z & ")"
If NbL > 1 Then Plage.Rows(2).Resize(NbL - 1).FormulaR1C1 = "=2*OFFSET(RC,-1,0)-OFFSET(RC,-2,0)"
End Sub