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
|
Option Explicit
Public TabDonnees() As Variant
Sub SauvDonnees()
'Début du chrono
Dim vchrono As Double
vchrono = Now()
'Désactive la mise à jour de l'affichage
Application.ScreenUpdating = False
'Désactive la mise à jour des recalculs
Application.Calculation = xlCalculationManual
'Déclaration des variables
Dim plage As Range, cellule As Range
Dim nbCells As Long
Dim i As Integer, j As Integer
Dim total As Double
'Initialisation des variables
Call TableauOPS
Erase TabDonnees
Set plage = Feuil5.Range("POSITION")
nbCells = plage.Rows.Count * plage.Columns.Count
ReDim TabDonnees(nbCells - 1, 7)
For Each cellule In plage
For i = LBound(TabDonnees, 1) To UBound(TabDonnees, 1)
TabDonnees(i, 0) = Feuil5.Range("Jour").text 'Date
TabDonnees(i, 1) = Feuil5.Cells(cellule.Row, Feuil5.Range("Horaire").Column).Value 'Position du personnel
TabDonnees(i, 2) = cellule.Value 'Nom du personnel
TabDonnees(i, 3) = Feuil2.Cells(TabTGR.Cells.Find(cellule.Value, LookIn:=xlValues).Row, TabQUALIF.Column) 'Qualification
TabDonnees(i, 4) = Feuil5.Cells(Feuil5.Range("Ouv").Row, cellule.Column).Value 'Debut
TabDonnees(i, 5) = Feuil5.Cells(Feuil5.Range("Ferm").Row, cellule.Column).Value 'Fin
total = Feuil5.Cells(Feuil5.Range("Ferm").Row, cellule.Column).Value - Feuil5.Cells(Feuil5.Range("Ferm").Row, cellule.Column).Value
TabDonnees(i, 6) = total 'Total
Next i
Next cellule
'Ré-activations
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
'Fin du chrono
vchrono = Now() - vchrono
MsgBox Format(vchrono, "h:mm:ss")
End Sub |
Partager