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
| Sub test()
Dim wb As Workbook
Dim ws As Worksheet, wD As Worksheet, wJ As Worksheet
Dim n_ligne As Long, n_ligne2 As Long, n_col As Integer 'variables dimensions
Dim i As Long, j As Integer, k As Long, jour As Integer, l As Integer, d As Long, a As Long, b As Long, c As Long, e As Long, n As Long
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Source")
Set wD = wb.Worksheets("Données horaires")
Set wJ = wb.Worksheets("Données journalières")
wD.Cells.Clear
'phase 1 on nettoie les données brutes
n_ligne = ws.Cells(Rows.Count, 1).End(xlUp).Row
n_col = ws.Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To 2
wD.Cells(1, j) = ws.Cells(1, j)
Next j
wD.Cells(1, 3) = ws.Cells(1, 4)
k = 3
For j = 8 To 10
wD.Cells(1, k + 1) = ws.Cells(1, j)
k = k + 1
Next j
j = 2 'ligne
b = 2 'permet de retrouver le type
n = wD.Cells(Rows.Count, 6).End(xlUp).Row
Do While b < n_ligne
Do While ws.Cells(j, 4).Value = ws.Cells(b, 4).Value
If ws.Cells(j, 4) <> "Autres" And ws.Cells(j, 4) <> "Bouclage" Then
For i = 1 To 24
For k = 1 To 2
wD.Cells(n + i, k).Value = ws.Cells(j, k).Value
Next k
wD.Cells(n + i, 3).Value = ws.Cells(j, 4).Value
For k = 8 To 9
wD.Cells(n + i, k - 4).Value = ws.Cells(j, k).Value
Next k
wD.Cells(n + i, 6).Value = wD.Cells(n + i, 6).Value + (ws.Cells(j, 10).Value + ws.Cells(j + 1, 10).Value) / 2
j = j + 2
Next i
End If
Loop
b = j + 2
Loop
End Sub |
