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
|
Option Base 1
Dim ws As Worksheet
Dim chaine
Dim tabl()
'--------------------------------------------------------------------------
' RECUPERE LES DONNEES DE LA 1ERE COLONNE POUR LES SCINDER EN 4 COLONNES
' DANS LE TABLEAU TABL()
'--------------------------------------------------------------------------
Sub creation_tableau()
Set ws = Worksheets(1)
Dim longchaine As Integer
Dim debchaine
Dim finchaine
Dim dernligne
Dim premligne
Dim tabrange
Dim i As Integer
premligne = ws.Cells(1, 1).End(xlDown).Row 'debut de selection
dernligne = ws.Cells(premligne, 1).End(xlDown).Row 'fin de la selection
tabrange = ws.Range("a" & premligne & ":" & "a" & dernligne) 'affectation tableau
ReDim tabl(UBound(tabrange, 1), 4)
For i = LBound(tabrange, 1) To UBound(tabrange, 1)
chaine = tabrange(i, 1)
If InStr(chaine, "JOURNEE DU") <> 0 Then
tabl(i, 1) = chaine
tabl(i, 2) = chaine
tabl(i, 3) = chaine
tabl(i, 4) = chaine
Else
'redisposer en 4 colonnes
'heure / EC / intitulé / état
longchaine = Len(Trim(chaine)) 'suppression espace + nb de carractères
chaine = Trim(Left(chaine, longchaine - 7)) 'suppression des chiffres a la fin de l expression
tabl(i, 1) = Trim(Left(chaine, 12))
tabl(i, 1) = TimeSerial(Left(tabl(i, 1), 2), Mid(tabl(i, 1), 4, 2), Mid(tabl(i, 1), 7, 2)) 'conversion en hh:mn:ss
debchaine = InStr(chaine, "*")
tabl(i, 2) = Trim(Mid(chaine, debchaine + 1, 14))
finchaine = InStr(chaine, "ENCE") - 5 '-5 pour le debut abscence ou presence
debchaine = InStr(chaine, "EC") + 2
tabl(i, 3) = Trim(Mid(chaine, debchaine, finchaine - debchaine))
tabl(i, 4) = Trim(Mid(chaine, finchaine, 10))
End If
Next i
Call calcul_tps
End Sub
Sub calcul_tps()
Dim top_presence
Dim top_abscence
Dim tps_alarme
Static tps_globale
Static jourdeplus As Variant
Dim i As Integer
Dim j As Integer
tps_globale = 0
jourdeplus = 0
For i = LBound(tabl, 1) To UBound(tabl, 1)
If tabl(i, 2) Like "5LCA009EC" And tabl(i, 4) Like "PRESENCE" Then
top_presence = tabl(i, 1)
For j = i + 1 To UBound(tabl, 1)
If InStr(tabl(j, 1), "JOURNEE DU") <> 0 Then
jourdeplus = jourdeplus + 1
End If
If tabl(j, 2) Like "5LCA009EC" And tabl(j, 4) Like "ABSENCE" Then
top_abscence = tabl(j, 1)
i = j
tps_alarme = DateDiff("s", top_presence, top_abscence + jourdeplus)
Exit For
End If
Next j
tps_globale = tps_globale + tps_alarme
End If
Next i
MsgBox jourdeplus
MsgBox tps_globale
End Sub |
