1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| Option Explicit
Sub Equivalence()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Feuil2") 'Feuille source..
Dim i As Integer 'index dans 1° tableau
Dim j As Integer 'index dans 2° tableau
Dim k As Integer 'index précédent du 2° tableau
j = 2
For i = 2 To sh.Range("A1").CurrentRegion.Rows.Count
Debug.Print sh.Cells(i, 1)
k = j
While sh.Cells(j, 8) <> "" And (sh.Cells(j, 8) < sh.Cells(i, 1))
k = j
j = j + 1
Wend
If sh.Cells(j, 8) <> "" Then
sh.Cells(i, 4) = Y3(sh.Cells(k, 8), sh.Cells(j, 8), sh.Cells(i, 1), sh.Cells(k, 9), sh.Cells(j, 9))
sh.Cells(i, 5) = Y3(sh.Cells(k, 8), sh.Cells(j, 8), sh.Cells(i, 1), sh.Cells(k, 10), sh.Cells(j, 10))
End If
Next
End Sub
Function Y3(X1 As Date, X2 As Date, X3 As Date, Y1 As Double, Y2 As Double) As Double
Dim A As Double
Dim B As Double
If (X1 = X2) Then
A = 0
B = Y1
Else
A = (Y1 - Y2) / (X1 - X2)
B = Y1 - (X1 * A)
End If
Y3 = X3 * A + B
End Function |
Partager