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
| Option Explicit
Sub transpose()
Dim a, b(), i As Long, j As Long, n As Long, x
With Sheets("Transposition TB").Range("a4").CurrentRegion
a = .Value
End With
ReDim b(1 To (((UBound(a, 2) - 2) / 2) * (UBound(a, 1) - 1)), 1 To 5)
For i = 2 To UBound(a, 1)
For j = 3 To UBound(a, 2) Step 2
n = n + 1
x = Split(a(1, j), "-")
If LCase(x(0)) = "fevrier" Then x(0) = "février"
b(n, 1) = a(i, 1): b(n, 2) = a(i, 2)
'b(n, 3) = x(0): b(n, 4) = a(i, j)
'ici le réajustement
b(n, 3) = Month(CDate("01/" & "" & x(0) & "")): b(n, 4) = a(i, j)
b(n, 5) = a(i, j + 1)
Next
Next
With Sheets(2).Cells(1).Resize(, 5)
.CurrentRegion.Clear
.Value = [{"Unité","Code Statut","Mois","Eff Prév","Etp Rém"}]
.Offset(1).Resize(n).Value = b
With .CurrentRegion
.Font.Name = "calibri"
.Font.Size = 10
.VerticalAlignment = xlCenter
.BorderAround Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
With .Rows(1)
.BorderAround Weight:=xlThin
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 36
.Font.Size = 11
End With
.Columns.ColumnWidth = 14
End With
.Parent.Activate
End With
End Sub |
Partager