j'ai écris la macro ci-dessous que je cherche à améliorer
car je la trouve longue
vous trouverez ci -joint le fichier qui contient en colonne F l'ensemble des formules qui correspondent au code que j'ai écris ci-dessous.
(en fait j'aurais pu utiliser directement ces formules mais elles s'effacent toutes lors de ma mise à jour ,raison pour laquelle j'ai fait la macro)
- Code: Tout sélectionner
Sub taux_spot_impl()
Dim i As Long, l As Long, C As Long, a As Long, lastrow As Long, j As Long, m As Long, b As Long
Dim r As Long, z As Long, k As Long, t As Long, n As Long
Dim ws As Worksheet
Set ws = Worksheets("Forwards")
k = 22
t = k + 1
lastrow = 730
j = 1
r = 130
b = 250
u = 142
For i = 23 To lastrow - 1
'premier pas k
If i <= 121 Then '(nous sommes entre le 13 ieme mois et le 10 ans)
ws.Cells(t + a, "E").Value = ws.Cells(k + a, "E").Value + _
(ws.Cells(t + a, "H").Value - ws.Cells(k + a, "H").Value) * ( _
(ws.Cells(k + 12 + a, "E").Value - ws.Cells(k + a, "E").Value) / ( _
ws.Cells(k + 12 + a, "H").Value - ws.Cells(k + a, "H").Value _
) _
)
'le compteur c
C = C + 1
t = t + 1
If C = 11 Then
a = a + 2
k = k + 10
t = t - 1
C = 0
End If
ElseIf i = 122 Then
For n = 142 To 250 Step 12
If n <= 238 Then
ws.Cells(n, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(b, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(b, "H").Value - ws.Cells(r, "H").Value _
) _
)
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
ElseIf n = 250 Then
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
End If
Next
ElseIf i = 123 Then
For n = 262 To 370 Step 12
If n <= 358 Then
ws.Cells(n, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(b, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(b, "H").Value - ws.Cells(r, "H").Value _
) _
)
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
ElseIf n = 370 Then
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
End If
Next
ElseIf i = 124 Then
For n = 382 To 490 Step 12
If n <= 478 Then
ws.Cells(n, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(b, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(b, "H").Value - ws.Cells(r, "H").Value _
) _
)
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
ElseIf n = 490 Then
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
End If
Next
ElseIf i = 125 Then
For n = 502 To 610 Step 12
If n <= 598 Then
ws.Cells(n, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(b, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(b, "H").Value - ws.Cells(r, "H").Value _
) _
)
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
ElseIf n = 610 Then
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
End If
Next
ElseIf i = 126 Then
For n = 622 To 730 Step 12
If n <= 718 Then
ws.Cells(n, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(b, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(b, "H").Value - ws.Cells(r, "H").Value _
) _
)
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
ElseIf n = 730 Then
For j = 11 To 1 Step -1
ws.Cells(n - j, "E").Value = ws.Cells(r, "E").Value + _
(ws.Cells(n - j, "H").Value - ws.Cells(r, "H").Value) * ( _
(ws.Cells(u, "E").Value - ws.Cells(r, "E").Value) / ( _
ws.Cells(u, "H").Value - ws.Cells(r, "H").Value _
) _
)
z = z + 1
If z = 11 Then
u = u + 12
r = r + 12
z = 0
End If
Next
End If
Next
End If
If i = 126 Then
Exit For
End If
If i >= 122 Then
b = b + 120
End If
Next
End Sub
cette macro tourne bien ,je veux juste la rendre plus courte en terme de ligne et plus efficace
merci bien
