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
| Option Explicit
Public Sub Taux_Occupation(ByRef cl2 As Workbook)
Dim f3 As Worksheet, objFeuille As Worksheet
Dim FeuilleExiste As Boolean
Dim cpt_ligne As Long, cpt_vert As Long, i As Long, j As Long, k As Long, L As Long
Dim taux_vert As Double
Dim LocalName As String, SwitchName As String
With cl2
'pout toutes les feuilles du classeur "DATE_Etat_Switch" si son nom est égale a celui du Local, FeuilleExiste_
'devient true sinon il ne change pas
For Each objFeuille In .Sheets
If objFeuille.Name = "Taux_d'occupation" Then
FeuilleExiste = True
Exit For
End If
Next
'si la feuille ayant le nom du local n'existe pas
If FeuilleExiste <> True Then
.Sheets.Add.Name = "Taux_d'occupation"
'*****MISE EN PAGE DU LOCAL*****'
'pour le titre du local qui est sur la premiere ligne de la feuille
With .Sheets("Taux_d'occupation")
With .Range(.Cells(1, 7), .Cells(1, 12))
.MergeCells = True
.Value = "Taux d'occupation des switch"
.Font.Name = "Arial"
.Font.Size = 22
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlMedium
End With
'couleur de fond blanc de la cellule A1 à BB500
.Range("A1:BB500").Interior.ColorIndex = 2
With .Cells(4, 2)
.Value = "Local"
.Font.Name = "Arial"
.Font.Size = 14
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlMedium
End With
With .Cells(4, 3)
.Value = "Switch"
.Font.Name = "Arial"
.Font.Size = 14
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlMedium
End With
With .Range(.Cells(4, 4), .Cells(4, 6))
.MergeCells = True
.Value = "Taux d'occupation"
.Font.Name = "Arial"
.Font.Size = 14
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlMedium
End With
End With
'*****FIN MISE EN PAGE DU LOCAL*****'
End If
Set f3 = .Sheets("Taux_d'occupation")
cpt_ligne = 5
For i = 1 To .Sheets.Count
cpt_vert = 0
If .Sheets(i).Name Like "CH*" Then
With .Sheets(i)
LocalName = .Name
For j = 3 To 100
If .Cells(j, 2).MergeCells = True Then
SwitchName = .Cells(j, 2).Value
MsgBox LocalName & " " & SwitchName
For k = j + 1 To j + 2
For L = 2 To 27
If .Cells(k, L).Interior.ColorIndex = 4 Or .Cells(k, L).Interior.ColorIndex = 45 Then
cpt_vert = cpt_vert + 1
End If
Next L
Next k
taux_vert = (cpt_vert * 100) / 48
f3.Cells(cpt_ligne, 2).Value = LocalName
With f3.Cells(cpt_ligne, 3)
.Value = SwitchName
.Font.Size = 12
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlMedium
End With
With f3.Range(f3.Cells(cpt_ligne, 4), f3.Cells(cpt_ligne, 6))
.MergeCells = True
.Value = taux_vert
.Font.Size = 12
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlMedium
End With
cpt_ligne = cpt_ligne + 1
End If
Next j
End With
End If
Next i
End With
End Sub |
