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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115
| Sub yo()
Dim test As Range
Dim dte As Date
Dim rdm(1 To 20) As Integer
Dim rdm_test As Integer
Dim flag1 As Integer
Dim flag2 As Integer
Dim flag3 As Integer
Dim upperbound As Integer
Dim lowerbound As Integer
Dim S(1 To 3, 1 To 4) As Single
Dim sum As Single
Dim ST1 As Single
With Worksheets("Feuil1")
Set test = .Range("A1")
test = "Emsemble 50"
test.Offset(0, 1) = "Sous-ensemble 1"
test.Offset(0, 2) = "S1 (Indice)"
test.Offset(0, 3) = "S1 (Value)"
test.Offset(0, 4) = "S2 (Indice)"
test.Offset(0, 5) = "S2 (Value)"
test.Offset(0, 6) = "S3 (Indice)"
test.Offset(0, 7) = "S3 (Value)"
test.Offset(0, 8) = "ST1"
upperbound = 55
lowerbound = 15
'Créer et place 50 variables aléatoires dans la colonne A
For i = 1 To 50
test.Offset(i, 0) = Application.Round((Int((upperbound - lowerbound + 1) * Rnd + lowerbound) / 1000), 3)
Next i
'Place 20 valeurs DIFFERENTES aléatoires dans la colonne B (tirées de l'ensemble des 50 valeurs)
upperbound = 50
lowerbound = 1
For i = 1 To 20
Do
flag1 = 0
rdm_test = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
For j = 1 To 20
If test.Offset(rdm_test).Value = test.Offset(rdm(j)).Value Then
flag1 = 1
End If
Next j
Loop Until flag1 = 0
rdm(i) = rdm_test
Next i
For i = 1 To UBound(rdm)
test.Offset(i, 1) = test.Offset(rdm(i), 0)
Next i
'Créer tes variables S1, S2 et S3 => S(1, 1 To 4) / S(2, 1 To 4) / S(3, 1 To 4)
'Créer aussi ST1
'=> le tout se fait "tant que" les conditions ne sont pas respectées et recalcule le tout
upperbound = 20
lowerbound = 1
Do
flag3 = 0
For k = 1 To 3
Do
flag2 = 0
For i = 1 To 4
Do
flag1 = 0
rdm_test = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
For j = 1 To 4
If rdm_test = S(1, j) Or rdm_test = S(2, j) Or rdm_test = S(3, j) Then
flag1 = 1
End If
Next j
Loop Until flag1 = 0
S(k, i) = rdm_test
test.Offset(i, 2 * k) = S(k, i)
test.Offset(i, 1 + (2 * k)) = test.Offset(S(k, i), 1)
Next i
sum = 0
For i = 1 To 4
sum = sum + test.Offset(S(k, i), 1)
Next i
If sum > 0.3 Then
flag2 = 1
End If
Loop Until flag2 = 0
test.Offset(5, 1 + (2 * k)) = Application.Round(sum, 3)
Next k
ST1 = 0
For i = 1 To 3
ST1 = ST1 + test.Offset(5, 1 + (2 * i))
Next i
If ST1 > 0.4 Then
flag3 = 1
End If
Loop Until flag3 = 0
test.Offset(1, 8) = ST1
End With
End Sub |
Partager