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 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189
| Sub Prio()
Worksheets("Feuil1").Activate
Dim PPm As Single, Student As Single, Gravite As String, Groupe As Single, MR As Single, i As Integer, j As Integer
PPm = 0
Student = 0
Gravite = ""
Groupe = 0
MR = 0
i = 2
j = 2
Do While Cells(i, 4).Value <> ""
PPm = (Cells(i, 26)) 'Nbr de ppm
Student = Cells(i, 22) 'Coefficient de student
Gravite = Cells(i, 12) 'PANNE ou INCIDENT
Groupe = Cells(i, 40) 'Groupe 1 ou 2
MR = Cells(i, 4) 'Nbr de mois de roulage
MsgBox (PPm)
Select Case MR
Case Is = 0 <= MR <= 1
MR = 1
Case Is = 1 < MR <= 3
MR = 3
Case Is = 3 < MR <= 12
MR = 12
Case Is = 12 < MR <= 24
MR = 24
End Select
'1er MR
Select Case MR
Case Is = 1
'Groupe 2
Select Case Groupe
Case Is = 2
Select Case PPm And Student And Gravite ' <= la valeur à tester (ici, la note)
Case Is = PPm >= 700 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 2
Case Is = 250 <= PPm < 700 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 0
Case Is = PPm >= 300 And Gravite = "PANNE"
Cells(j, 41).Value = 2
Case Is = 100 <= PPm < 300 And Gravite = "PANNE" And Student >= 5
Cells(j, 41).Value = 2
Case Else
Cells(j, 41).Value = 1
End Select
'Groupe 1
Case Is = 1
Select Case PPm And Student And Gravite ' <= la valeur à tester (ici, la note)
Case Is = PPm >= 700 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 3
Case Is = 250 <= PPm < 700 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 1
Case Is = 100 <= PPm < 250 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 1
Case Is = 100 <= PPm < 250 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 0
Case Is = PPm >= 300 And Gravite = "PANNE"
Cells(j, 41).Value = 3
Case Is = 100 <= PPm < 300 And Gravite = "PANNE" And Student >= 5
Cells(j, 41).Value = 3
Case Is = 25 <= PPm < 100 And Gravite = "PANNE" And 0 < Student < 5
Cells(j, 41).Value = 1
Case Else
Cells(j, 41).Value = 2
End Select
End Select
'3 MR
Case Is = 3
Select Case Groupe
Case Is = 2
Select Case PPm And Student And Gravite ' <= la valeur à tester (ici, la note)
Case Is = PPm >= 2000 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 2
Case Is = 750 <= PPm < 2000 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 0
Case Is = PPm >= 800 And Gravite = "PANNE"
Cells(j, 41).Value = 2
Case Is = 200 <= PPm < 800 And Gravite = "PANNE" And Student >= 5
Cells(j, 41).Value = 2
Case Else
Cells(j, 41).Value = 1
End Select
'Groupe 1
Case Is = 1
Select Case PPm And Student And Gravite ' <= la valeur à tester (ici, la note)
Case Is = PPm >= 2000 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 3
Case Is = 750 <= PPm < 2000 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 1
Case Is = 250 <= PPm < 750 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 1
Case Is = 250 <= PPm < 750 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 0
Case Is = PPm >= 800 And Gravite = "PANNE"
Cells(j, 41).Value = 3
Case Is = 200 <= PPm < 800 And Gravite = "PANNE" And Student >= 5
Cells(j, 41).Value = 3
Case Is = 50 <= PPm < 200 And Gravite = "PANNE" And 0 < Student < 5
Cells(j, 41).Value = 1
Case Else
Cells(j, 41).Value = 2
End Select
End Select
'12 MR
Case Is = 12
Select Case Groupe
Case Is = 2
Select Case PPm And Student And Gravite ' <= la valeur à tester (ici, la note)
Case Is = PPm >= 8000 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 2
Case Is = 3000 <= PPm < 8000 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 0
Case Is = PPm >= 2400 And Gravite = "PANNE"
Cells(j, 41).Value = 2
Case Is = 600 <= PPm < 2400 And Gravite = "PANNE" And Student >= 5
Cells(j, 41).Value = 2
Case Else
Cells(j, 41).Value = 1
End Select
'Groupe 1
Case Is = 1
Select Case PPm And Student And Gravite ' <= la valeur à tester (ici, la note)
Case Is = PPm >= 8000 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 3
Case Is = 3000 <= PPm < 8000 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 1
Case Is = 1000 <= PPm < 3000 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 1
Case Is = 1000 <= PPm < 3000 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 0
Case Is = PPm >= 2400 And Gravite = "PANNE"
Cells(j, 41).Value = 3
Case Is = 600 <= PPm < 2400 And Gravite = "PANNE" And Student >= 5
Cells(j, 41).Value = 3
Case Is = 150 <= PPm < 600 And Gravite = "PANNE" And 0 < Student < 5
Cells(j, 41).Value = 1
Case Else
Cells(j, 41).Value = 2
End Select
End Select
'24 MR
Case Is = 24
'Meme grille prio pour Gr1 et Gr2
Select Case PPm And Student And Gravite ' <= la valeur à tester (ici, la note)
Case Is = PPm >= 8000 And Gravite = "INCIDENT" And Student >= 5
Cells(j, 41).Value = 2
Case Is = 9000 <= PPm < 24000 And Gravite = "INCIDENT" And 0 < Student < 5
Cells(j, 41).Value = 0
Case Is = PPm >= 5500 And Gravite = "PANNE"
Cells(j, 41).Value = 2
Case Is = 1500 <= PPm < 5500 And Gravite = "PANNE" And Student >= 5
Cells(j, 41).Value = 2
Case Else
Cells(j, 41).Value = 1
End Select
End Select
j = j + 1
i = i + 1
Loop
Worksheets("Feuil2").Activate
End Sub |
Partager