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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
| Sub update1()
Dim i, j, k, l, m As Long
Dim S3, S4 As Worksheet
Set S3 = Sheets("calcul")
Set S4 = Sheets("ETF 1")
k = 3
For j = 3 To 52
For l = 15 To 20
For i = 15 To 24
S4.Cells(8, 22) = S3.Cells(2, j)
S4.Cells(7, 3) = S3.Cells(5, j)
S4.Cells(9, 3) = S3.Cells(4, j)
S4.Cells(i, 6) = Application.VLookup(S4.Cells(i, 3), S3.Range(S3.Cells(2, 1), S3.Cells(83, 52)), k, False)
S4.Cells(i, 12) = Application.VLookup(S4.Cells(i, 9), S3.Range(S3.Cells(2, 1), S3.Cells(83, 52)), k, False)
Next i
S4.Cells(l, 18) = Application.VLookup(S4.Cells(l, 15), S3.Range(S3.Cells(2, 1), S3.Cells(83, 52)), k, False)
S4.Cells(21, 18) = "Y"
Next l
k = k + 1
update2(j, j)
Next j
End Sub
Sub update2(jInit As Long, mInit As Long)
Dim S3, S4 As Worksheet
Set S3 = Sheets("calcul")
Set S4 = Sheets("ETF 1")
For j = jInit To 52
For m = mInit To 52
S4.Cells(8, 22) = S3.Cells(2, j)
S4.Range(S4.Cells(28, 6), S4.Cells(34, 6)).Value = S3.Range(S3.Cells(155, m), S3.Cells(161, m)).Value
S4.Range(S4.Cells(28, 9), S4.Cells(34, 9)).Value = S3.Range(S3.Cells(124, m), S3.Cells(133, m)).Value
S4.Range(S4.Cells(28, 12), S4.Cells(34, 12)).Value = S3.Range(S3.Cells(134, m), S3.Cells(143, m)).Value
S4.Range(S4.Cells(28, 18), S4.Cells(30, 18)).Value = S3.Range(S3.Cells(234, m), S3.Cells(236, m)).Value
S4.Range(S4.Cells(38, 3), S4.Cells(47, 3)).Value = S3.Range(S3.Cells(164, m), S3.Cells(173, m)).Value
S4.Range(S4.Cells(38, 6), S4.Cells(47, 6)).Value = S3.Range(S3.Cells(174, m), S3.Cells(183, m)).Value
S4.Range(S4.Cells(38, 9), S4.Cells(47, 9)).Value = S3.Range(S3.Cells(104, m), S3.Cells(113, m)).Value
S4.Range(S4.Cells(38, 12), S4.Cells(47, 12)).Value = S3.Range(S3.Cells(114, m), S3.Cells(123, m)).Value
S4.Range(S4.Cells(38, 15), S4.Cells(47, 15)).Value = S3.Range(S3.Cells(84, m), S3.Cells(93, m)).Value
S4.Range(S4.Cells(38, 18), S4.Cells(47, 18)).Value = S3.Range(S3.Cells(94, m), S3.Cells(103, m)).Value
Next m
Next j
End Sub |
Partager