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
| Sub GestionCaseTest()
' déclaration de variables
Dim equipe As String
Dim unite As Integer
Dim var1 As Integer 'numero de ligne
'Dim ligne As Variant
Dim compt As Integer
Dim feuille As Worksheet
Dim temps_debut As Single
Dim duree As Single
temps_debut = Timer
Set feuille = ThisWorkbook.Worksheets("Tableau")
Dim ArrayTableau() As String
Dim indice As Integer
indice = 0
Dim i As Integer
Dim a As Integer
' améliorer vitesse d'excécution
Dim ecran As Boolean, statutbarre As Boolean, even As Boolean
ecran = Application.ScreenUpdating '= False
statutbarre = Application.DisplayStatusBar '= False
even = Application.EnableEvents '= False
' je mets tout sur OFF
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
' initialisation variables
unite = Worksheets("Data").Range("AC6").Value
equipe = Worksheets("Data").Range("S6").Value
If unite = 1 Then '
Worksheets("Tableau").Select
Worksheets("Tableau").Activate
Select Case equipe
Case 1
Worksheets("Tableau").Rows("12:510").Hidden = True
For compt = 12 To 510
If Cells(compt, 2).Value = "O" Or Cells(compt, 3) = "Nord" Then
indice = indice + 1
ReDim Preserve ArrayTableau(indice)
ArrayTableau(indice) = compt
End If
Next compt
' afficher ArrayTableau
For i = 1 To indice
a = ArrayTableau(i)
Worksheets("Tableau").Rows(a).Hidden = False
Next i
'----------------------------
Case 2
feuille.Rows("12:510").Hidden = True
For compt = 12 To 510
If feuille.Cells(compt, 4) = "Sil" Then
'feuille.Rows(compt).Hidden = True
indice = indice + 1
ReDim Preserve ArrayTableau(indice)
ArrayTableau(indice) = compt
End If
Next compt
' afficher ArrayTableau
For i = 1 To indice
a = ArrayTableau(i)
Worksheets("Tableau").Rows(a).Hidden = False
Next i
'afficher et afficher la durée d'excucution :
duree = Timer - temps_debut
Worksheets("Tableau").Range("G2") = duree
'----------------------------
'résultats équipe
Worksheets("Tableau").Rows("512:580").Hidden = True
Worksheets("Tableau").Rows("512:515").Hidden = False
Worksheets("Tableau").Rows("531:535").Hidden = False
'afficher et afficher la durée d'excucution :
duree = Timer - temps_debut
Worksheets("Tableau").Range("G3") = duree
Case 3
feuille.Rows("12:510").Hidden = True
For compt = 12 To 510
If feuille.Cells(compt, 4) = "EM" Then
indice = indice + 1
ReDim Preserve ArrayTableau(indice)
ArrayTableau(indice) = compt
End If
Next compt
' afficher ArrayTableau
For i = 1 To indice
a = ArrayTableau(i)
Worksheets("Tableau").Rows(a).Hidden = False
Next i
' afficher et afficher la durée d'excucution :
duree = Timer - temps_debut
Worksheets("Tableau").Range("G2") = duree
'----------------------------
'afficher et afficher la durée d'excucution :
duree = Timer - temps_debut
Worksheets("Tableau").Range("G3") = duree
Case Else '"tous les autres cas"
MsgBox ("voir GestionCasautrecas")
Exit Sub
End Select
'
End If
' je restaure
Application.ScreenUpdating = ecran
Application.DisplayStatusBar = statutbarre
Application.EnableEvents = even
Application.Calculation = xlCalculationSemiautomatic
End Sub |
Partager