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
| Sub TransposeByDate(rngFrom As Range, rngTo As Range, Optional EmptyColumn As Boolean)
' rngFrom ' Range de base ' ex A2:B15
' rngTo ' Cellule de transfert ' ex F2
' [EmptyColumn] ' Valeur Booleenne si TRUE garde les mois non rempli (False par défaut)
Dim sValue As Long, gValue As Long
Dim rngC As Range: Set rngC = rngFrom.Offset(1, 1).Resize(rngFrom.Rows.Count - 1, 1)
Dim m As Integer, nbMonth As Integer, r As Long, nbRow As Integer
Dim addr As String
Dim rngMove As Range
Dim ValueByDate()
If rngFrom.Columns.Count <> 2 Then MsgBox "2 colonnes requises - Sortie de procédure": Exit Sub
Application.ScreenUpdating = False
rngTo.CurrentRegion.Clear
With Application.WorksheetFunction
sValue = .Small(rngC, 1): gValue = .Large(rngC, 1)
End With
nbMonth = DateDiff("m", sValue, gValue)
ReDim ValueByDate(nbMonth)
With rngTo
.Offset(0, 0) = DateSerial(Year(sValue), Month(sValue), 1)
For m = 1 To nbMonth
.Offset(0, m) = DateAdd("m", 1, .Offset(0, m - 1))
Next
End With
With rngFrom
For r = 1 To .Rows.Count - 1
m = DateDiff("m", sValue, .Parent.Cells(.Row + r, .Column + 1))
Set rngMove = rngTo.Parent.Range(rngTo.Offset(0, m).Resize(rngTo.CurrentRegion.Rows.Count, 1).Address)
nbRow = Application.WorksheetFunction.CountA(rngMove)
rngTo.Offset(nbRow, m) = .Parent.Cells(.Row + r, .Column) ' Valeur
Next
End With
' Clear des plages non remplies si EmptyColumn = False
If Not (EmptyColumn) Then
For m = nbMonth To 1 Step -1
Set rngMove = rngTo.Parent.Range(rngTo.Offset(0, m).Resize(rngTo.CurrentRegion.Rows.Count, 1).Address)
nbRow = Application.WorksheetFunction.CountA(rngMove)
If nbRow = 1 Then rngMove.Delete Shift:=xlToLeft
Next
End If
Application.ScreenUpdating = False
End Sub |
Partager