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
| Public Function taux_occupation(cl2)
Dim f3 As Worksheet
cl2.Activate
FeuilleExiste = False
'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 cl2.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 = False) Then
'création d'une feuille
Sheets.Add
'renomme la feuille avec le nom du local
ActiveSheet.Name = "Taux_d'occupation"
'*****MISE EN PAGE DU LOCAL*****'
'pour le titre du local qui est sur la premiere ligne de la feuille
Range(Cells(1, 7), Cells(1, 12)).Select
With Selection
.MergeCells = True
.Value = "Taux d'occupation des switch"
.Font.Name = "Arial"
.Font.Size = 22
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With
'couleur de fond blanc de la cellule A1 à BB500
Range("A1:BB500").Interior.ColorIndex = 2
Cells(4, 2).Select
With Selection
.Value = "Local"
.Font.Name = "Arial"
.Font.Size = 14
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With
Cells(4, 3).Select
With Selection
.Value = "Switch"
.Font.Name = "Arial"
.Font.Size = 14
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With
Range(Cells(4, 4), Cells(4, 6)).Select
With Selection
.MergeCells = True
.Value = "Taux d'occupation"
.Font.Name = "Arial"
.Font.Size = 14
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With
'*****FIN MISE EN PAGE DU LOCAL*****'
End If
Set f3 = cl2.Worksheets("Taux_d'occupation")
cpt_ligne = 5
For i = 1 To Sheets.Count
cpt_vert = 0
If Sheets(i).Name Like "CH*" Then
Sheets(i).Activate
LocalName = Sheets(i).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
f3.Cells(cpt_ligne, 3).Select
With Selection
.Value = SwitchName
.Font.Size = 12
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With
f3.Range(Cells(cpt_ligne, 4), Cells(cpt_ligne, 6)).Select
With Selection
.MergeCells = True
.Value = taux_vert
.Font.Size = 12
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With
cpt_ligne = cpt_ligne + 1
'||||||||||||||||||||
End If
Next j
End If
Next i
End Function |
Partager