Sub Regression()
Dim derlig%, dercol%, i%, j%, Xc(), Yc(), n%
derlig = Range("A65536").End(xlUp).Row
dercol = Range("Pente").Column - 1
Application.ScreenUpdating = False
Intersect(Range("Pente"), Range("2:65536")).Resize(, 2).ClearContents
For i = 2 To derlig
ReDim Xc(0): ReDim Yc(0): n = 0
'---Détermine les valeurs connues Xc et Yc---
For j = 2 To dercol
If Cells(i, j) <> "" Then
ReDim Preserve Xc(n): Xc(n) = Cells(1, j).Value2
ReDim Preserve Yc(n): Yc(n) = Cells(i, j)
n = n + 1
End If
Next
'---Calcul de DROITEREG---
Cells(i, dercol + 1) = Application.LinEst(Yc, Xc) 'pente a
Cells(i, dercol + 2) = Application.LinEst(Yc, Xc)(2) 'ordonnée à l'origine b
Next
End Sub