maVar = (g(i) * Worksheets("Forwards").Cells(x2(i) + 11, 7).Value _ + 12 * (j - 1) + (30 - g(i)) * _ (Worksheets("Forwards").Cells(x1(i) + 11, 7).Value _ + 12 * (j - 1))) / 30
Dim x() As Double, T() As Double Dim p() As Double, g() As Double Dim i As Integer, j As Integer Dim x1() As Integer, x2() As Integer Dim v() As Integer Dim somme() As Single ReDim x(6 To Cells(Rows.Count, 1).End(xlUp).Row), T(6 To Cells(Rows.Count, 1).End(xlUp).Row) ReDim p(6 To Cells(Rows.Count, 1).End(xlUp).Row), g(6 To Cells(Rows.Count, 1).End(xlUp).Row) ReDim v(6 To Cells(Rows.Count, 1).End(xlUp).Row) ReDim x1(6 To Cells(Rows.Count, 1).End(xlUp).Row), x2(6 To Cells(Rows.Count, 1).End(xlUp).Row) ReDim somme(6 To Cells(Rows.Count, 1).End(xlUp).Row)
T(j + 5) = (g(i) * (Worksheets("Forwards").Cells(x2(i) + 11, 7).Value _ + 12 * (j - 1)) + (30 - g(i)) * _ (Worksheets("Forwards").Cells(x1(i) + 11, 7).Value _ + 12 * (j - 1))) / 30
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionT(j + 5) = ( _ g(i) * ( _ Worksheets("Forwards").Cells(X2(i) + 11, 7).Value _ + 12 * (j - 1) _ ) _ + (30 - g(i)) * ( _ Worksheets("Forwards").Cells(X1(i) + 11, 7).Value _ + 12 * (j - 1) _ ) _ ) / 30Plus facile à relire et à comprendre, non ?
T(j + 5) = ( _ g(i) * ( _ Worksheets("Forwards").Cells(X2(i) + 11, 7).Value _ + 12 * (j - 1) _ ) _ + (30 - g(i)) * ( _ Worksheets("Forwards").Cells(X1(i) + 11, 7).Value _ + 12 * (j - 1) _ ) _ ) / 30
Sub Prixspot() Stop Dim k As Long Dim x() As Double, T() As Double Dim p() As Double, g() As Double Dim i As Integer, j As Integer Dim x1() As Integer, x2() As Integer Dim v() As Integer Dim somme() As Single Dim forwards_x2_11x7 As Double Dim forwards_x1_11x7 As Double k = Cells(Rows.Count, 1).End(xlUp).Row ReDim x(6 To k), T(6 To k) ReDim p(6 To k), g(6 To k) ReDim v(6 To k) ReDim x1(6 To k), x2(6 To k) ReDim somme(6 To k) For i = 6 To k somme(i) = 0 x(i) = Cells(i, 15).Value / 30 'conversion du nombre de jours en mois a la ligne i x1(i) = Int(x(i)) 'partie entiere de x(i) x2(i) = x1(i) + 1 v(i) = Cells(i, 14).Value ' nombre d'année de la ligne i g(i) = (x(i) - x1(i)) * 30 forwards_x2_11x7 = Worksheets("Forwards").Cells(x2(i) + 11, 7).Value forwards_x1_11x7 = Worksheets("Forwards").Cells(x1(i) + 11, 7).Value If v(i) > 0 Then For j = 1 To v(i) p(j + 5) = x(i) / 12 + (j - 1) T(j + 5) = (g(i) * (forwards_x2_11x7 + 12 * (j - 1)) + (30 - g(i)) * (forwards_x1_11x7 + 12 * (j - 1))) / 30 somme(i) = Cells(i, 13).Value / (1 + T(j + 5)) ^ p(j + 5) + 100 / (1 + T(v(i)) ^ p(v(i))) Next j End If somme(i) = somme(i) Cells(i, 11).Value = somme(i) Next i End Sub
... / (1 + T(j + 5)) ^ p(j + 5) + 100 / (1 + T(v(i)+5) ^ p(v(i)+5))
v()=[5,1,6,4,....,3] donc v(6)=5,v(7)=1,.....,v(195)=3
x()=[10.33,11.55,8,...,12.33,12] x(6)=10.33, x(7)=11.55 ,..,x(195)=12
somme()=[................]qui est ma colonne K toujours de la ligne 6 a la ligne 195
x1()=Int(x)=[10,11,8,...,12,12] (int=partie entiere de x) donc x1(6)=10,x1(7)=11... x2()=x1()+1=[11,12,9,...,13,13] x2(6)=11,... g()=(x()-Int(x()))*30=[0.33*30,0.55*30,0,...,0.33*30,0] donc g(6)=0.33*30,...
x(6)=10.33;x1(6)=10;x2(6)=11;v(6)=5; g(6)=0.33*30donc je calcule selon la boucle ci dessous:
For j = 1 To v(i) p(j + 5) = x(i) / 12 + (j - 1) T(j + 5) = (g(i) * (forwards_x2_11x7 + 12 * (j - 1)) + (30 - g(i)) * (forwards_x1_11x7 + 12 * (j - 1))) / 30 somme(i) = Cells(i, 13).Value / (1 + T(j + 5)) ^ p(j + 5) + 100 / (1 + T(v(i) + 5) ^ p(v(i) + 5)) Next j
p(6)=x(6)/12;p(7)=x(6)/12+1;p(8)=x(6)/12+2;p(9)=x(6)/12+3;p(10)=x(6)/12+4 puis je calcule T(6)=(g(6) * (forwards_x2_11x7 ) + (30 - g(6)) * (forwards_x1_11x7 )) / 30 T(7)=(g(6) * (forwards_x2_11x7 + 12 ) + (30 - g(6)) * (forwards_x1_11x7 + 12 )) / 30 T(8)=(g(6) * (forwards_x2_11x7 + 12 * 2) + (30 - g(6)) * (forwards_x1_11x7 + 12 * 2)) / 30 T(9)=(g(6) * (forwards_x2_11x7 + 12 * 3) + (30 - g(6)) * (forwards_x1_11x7 + 12 * 3)) / 30 T(10)=(g(6) * (forwards_x2_11x7 + 12 * 4) + (30 - g(6)) * (forwards_x1_11x7 + 12 * 4)) / 30
somme(6)=cells(6,13).value*(1/(1+T(6))^p(6)+1/(1+T(7))^p(7)+1/(1+T(8))^p(8)+1/(1+T(9))^p(9)+1/(1+T(10))^p(10))+100/(1+T(10))^p(10)
somme(6) dans cells(6,11)
Sub Prixspot() Dim k As Long Dim x() As Double Dim T() As Double Dim p() As Double, g() As Double Dim i As Integer, j As Integer Dim x1() As Integer, x2() As Integer Dim v() As Integer Dim somme() As Single Dim forwards_x2_11x7 As Double Dim forwards_x1_11x7 As Double k = Cells(Rows.Count, 1).End(xlUp).Row ReDim x(6 To k), T(6 To k) ReDim p(6 To k), g(6 To k) ReDim v(6 To k) ReDim x1(6 To k), x2(6 To k) ReDim somme(6 To k) For i = 6 To k If Cells(i, 15).Value <> "" Then somme(i) = 0 x(i) = Cells(i, 15).Value 'conversion du nombre de jours en mois a la ligne i x1(i) = Int(x(i)) 'partie entiere de x(i) x2(i) = x1(i) + 1 v(i) = Cells(i, 14).Value ' nombre d'année de la ligne i g(i) = (x(i) - x1(i)) * 30 forwards_x2_11x7 = Worksheets("Forwards").Cells(x2(i) + 11, 7).Value forwards_x1_11x7 = Worksheets("Forwards").Cells(x1(i) + 11, 7).Value If v(i) > 0 Then For j = 1 To v(i) p(j + 5) = x(i) / 12 + (j - 1) T(j + 5) = (g(i) * (forwards_x2_11x7 + 12 * (j - 1)) + (30 - g(i)) * (forwards_x1_11x7 + 12 * (j - 1))) / 30 somme(i) = Cells(i, 13).Value / (1 + T(j + 5)) ^ p(j + 5) + 100 / (1 + T(v(i) + 5) ^ p(v(i) + 5)) Next j End If somme(i) = somme(i) Cells(i, 11).Value = somme(i) End If Next i End sub
If v(i) > 0 Then For j = 1 To v(i) p(j + 5) = x(i) / 12 + (j - 1) T(j + 5) = (g(i) * (forwards_x2_11x7 + 12 * (j - 1)) + (30 - g(i)) * (forwards_x1_11x7 + 12 * (j - 1))) / 3000 somme(i) = somme(i) + Cells(i, 13).Value / (1 + T(j + 5)) ^ p(j + 5) Next j somme(i) = somme(i) + 100 / (1 + T(v(i) + 5) ^ p(v(i) + 5)) Cells(i, 11).Value = somme(i)