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 190 191 192 193 194 195 196 197 198 199 200 201 202
| '==================================================
' Affichage Rupture CQ
'==================================================
Sub Liste_Rupture()
Dim DATE_JOUR
Dim DL_TF As Long
Dim DL_PETRI As Long
Dim DL_MS As Long
Dim DL_APPRO As Long
Dim DL_RUPTURE As Long
Dim monTab() As Variant
' suppression des filtres
If ThisWorkbook.Sheets("Liste T&F").AutoFilterMode = True Then
ThisWorkbook.Sheets("Liste T&F").AutoFilterMode = False
End If
If ThisWorkbook.Sheets("Liste PETRI").AutoFilterMode = True Then
ThisWorkbook.Sheets("Liste PETRI").AutoFilterMode = False
End If
If ThisWorkbook.Sheets("Liste Appro Marcy").AutoFilterMode = True Then
ThisWorkbook.Sheets("Liste Appro Marcy").AutoFilterMode = False
End If
If ThisWorkbook.Sheets("Liste Milieu Sec").AutoFilterMode = True Then
ThisWorkbook.Sheets("Liste Milieu Sec").AutoFilterMode = False
End If
If ThisWorkbook.Sheets("Rupture").AutoFilterMode = True Then
ThisWorkbook.Sheets("Rupture").AutoFilterMode = False
End If
'remise à 0 de Rupture
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Cells(Rows.Count, 1).End(xlUp).Row
If DL_RUPTURE <> 1 Then
ThisWorkbook.Worksheets("Rupture").Range("A2:F" & DL_RUPTURE).ClearContents
ThisWorkbook.Worksheets("Rupture").Range("A2:A" & DL_RUPTURE).Font.Bold = False
End If
DATE_JOUR = Date
DL_TF = ThisWorkbook.Worksheets("Liste T&F").Cells(Rows.Count, 3).End(xlUp).Row
DL_PETRI = ThisWorkbook.Worksheets("Liste PETRI").Cells(Rows.Count, 3).End(xlUp).Row
DL_MS = ThisWorkbook.Worksheets("Liste Milieu Sec").Cells(Rows.Count, 3).End(xlUp).Row
DL_APPRO = ThisWorkbook.Worksheets("Liste Appro Marcy").Cells(Rows.Count, 3).End(xlUp).Row
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("B").Find("", , , , xlByColumns, xlNext).Row
't&f
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = "Rupture T&F"
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1).Font.Bold = True
monTab() = ThisWorkbook.Worksheets("Liste T&F").Range("A3:AM" & DL_TF).Value
For i = LBound(monTab, 1) To UBound(monTab, 1)
If (monTab(i, 24) = "" And (monTab(i, 4) = "Rupture" Or monTab(i, 4) = "Critique")) And ((monTab(i, 11) = "" And monTab(i, 10) <= DATE_JOUR) Or (monTab(i, 21) = "" And monTab(i, 20) <= DATE_JOUR)) Then
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = monTab(i, 1)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 2) = monTab(i, 3)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 3) = monTab(i, 4)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = monTab(i, 10)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = monTab(i, 20)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 6) = monTab(i, 2)
If monTab(i, 11) <> "" Or monTab(i, 5) = "N/A" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Stérilité terminée"
End If
If monTab(i, 21) <> "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Activité terminée"
End If
If monTab(i, 5) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Pas d'infos Stérilité"
End If
If monTab(i, 17) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Pas d'infos Activité"
End If
End If
Next i
Erase monTab
'petri
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = "Rupture PETRI"
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1).Font.Bold = True
monTab() = ThisWorkbook.Worksheets("Liste PETRI").Range("A3:AC" & DL_PETRI).Value
For i = LBound(monTab, 1) To UBound(monTab, 1)
If (monTab(i, 21) = "" And (monTab(i, 4) = "Rupture" Or monTab(i, 4) = "Critique")) And ((monTab(i, 8) = "" And monTab(i, 7) <= DATE_JOUR) Or (monTab(i, 18) = "" And monTab(i, 17) <= DATE_JOUR)) Then
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = monTab(i, 1)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 2) = monTab(i, 3)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 3) = monTab(i, 4)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = monTab(i, 7)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = monTab(i, 17)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 6) = monTab(i, 2)
If monTab(i, 8) <> "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Stérilité terminée"
End If
If monTab(i, 18) <> "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Activité terminée"
End If
If monTab(i, 5) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Pas d'infos Stérilité"
End If
If monTab(i, 14) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Pas d'infos Activité"
End If
End If
Next i
Erase monTab
'MS
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = "Rupture Milieu Sec"
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1).Font.Bold = True
monTab() = ThisWorkbook.Worksheets("Liste Milieu Sec").Range("A3:AK" & DL_MS).Value
For i = LBound(monTab, 1) To UBound(monTab, 1)
If monTab(i, 29) = "" And (monTab(i, 4) = "Rupture" Or monTab(i, 4) = "Critique") And ((monTab(i, 11) <> "N" And monTab(i, 15) = "" And monTab(i, 14) <= DATE_JOUR) Or (monTab(i, 20) <> "N" And monTab(i, 26) = "" And monTab(i, 25) <= DATE_JOUR)) Then
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row 'ligne qui bug....
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = monTab(i, 1)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 2) = monTab(i, 3)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 3) = monTab(i, 4)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = monTab(i, 14)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = monTab(i, 25)
If monTab(i, 11) = "N" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Pas de Stérilité"
End If
If monTab(i, 20) = "N" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Pas d'activité"
End If
If monTab(i, 11) = "O" Then
If monTab(i, 15) <> "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Stérilité terminée"
End If
If monTab(i, 12) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Pas d'infos Stérilité"
End If
End If
If monTab(i, 20) = "O" Then
If monTab(i, 26) <> "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Activité terminée"
End If
If monTab(i, 22) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Pas d'infos Activité"
End If
End If
End If
Next i
Erase monTab
'Appro
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = "Rupture Appro Marcy"
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1).Font.Bold = True
monTab() = ThisWorkbook.Worksheets("Liste Appro Marcy").Range("A3:AK" & DL_APPRO).Value
For i = LBound(monTab, 1) To UBound(monTab, 1)
If monTab(i, 29) = "" And (monTab(i, 4) = "Rupture" Or monTab(i, 4) = "Critique") And ((monTab(i, 11) <> "N" And monTab(i, 15) = "" And monTab(i, 14) <= DATE_JOUR) Or (monTab(i, 20) <> "N" And monTab(i, 26) = "" And monTab(i, 25) <= DATE_JOUR)) Then
DL_RUPTURE = ThisWorkbook.Worksheets("Rupture").Columns("A").Find("", , , , xlByColumns, xlNext).Row
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 1) = monTab(i, 1)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 2) = monTab(i, 3)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 3) = monTab(i, 4)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = monTab(i, 14)
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = monTab(i, 25)
If monTab(i, 11) = "N" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Pas de Stérilité"
End If
If monTab(i, 20) = "N" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Pas d'activité"
End If
If monTab(i, 11) = "O" Then
If monTab(i, 15) <> "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Stérilité terminée"
End If
If monTab(i, 12) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 4) = "Pas d'infos Stérilité"
End If
End If
If monTab(i, 20) = "O" Then
If monTab(i, 26) <> "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Activité terminée"
End If
If monTab(i, 22) = "" Then
ThisWorkbook.Worksheets("Rupture").Cells(DL_RUPTURE, 5) = "Pas d'infos Activité"
End If
End If
End If
Next i
Erase monTab
' remise des filtres
If ThisWorkbook.Sheets("Liste T&F").AutoFilterMode = False Then
ThisWorkbook.Worksheets("Liste T&F").Range("A2:AM2").AutoFilter
End If
If ThisWorkbook.Sheets("Liste PETRI").AutoFilterMode = False Then
ThisWorkbook.Worksheets("Liste PETRI").Range("A2:AC2").AutoFilter
End If
If ThisWorkbook.Sheets("Liste Appro Marcy").AutoFilterMode = False Then
ThisWorkbook.Worksheets("Liste Appro Marcy").Range("A2:AK2").AutoFilter
End If
If ThisWorkbook.Sheets("Liste Milieu Sec").AutoFilterMode = False Then
ThisWorkbook.Worksheets("Liste Milieu Sec").Range("A2:AK2").AutoFilter
End If
If ThisWorkbook.Sheets("Rupture").AutoFilterMode = False Then
ThisWorkbook.Worksheets("Rupture").Range("A1:F1").AutoFilter
End If
ThisWorkbook.Worksheets("Rupture").Activate
ThisWorkbook.Worksheets("Rupture").Range("A1").Select
End Sub |
Partager