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
| Sub Pret_Emprunt()
Dim c As Range, Ctr As Double, x As Range, Somme As Double, Moyenne As Single
Dim ligne As Long, LigneDeb As Long, LigneCred As Long
Dim Min As Date, Dico As Object, Plage As Range
ligne = 2
Set Dico = Nothing
Set Dico = CreateObject("Scripting.Dictionary")
For Each c In Range([E4], Cells(Rows.Count, 5).End(xlUp))
If Not Dico.exists(DateSerial(Year(c.Value), Month(c.Value), 1)) Then
Dico.Add DateSerial(Year(c.Value), Month(c.Value), 1), _
DateSerial(Year(c.Value), Month(c.Value), 1)
End If
Next c
i = 0
For Each Item In Dico.items
i = i + 1
Cells(i, "AH") = Item
Next Item
[AH:AH].Sort Range("AH1"), xlAscending, Header:=xlNo
For Each x In Range([AH1], Cells(Rows.Count, "AH").End(xlUp))
Ctr = 0
ligne = ligne + 2
Cells(ligne, 40) = DateSerial(Year(x.Value), Month(x.Value), 1)
Cells(ligne, 40).NumberFormat = "mmm-yyyy"
LigneDeb = ligne
LigneCred = ligne
For Each c In Range([E4], Cells(Rows.Count, 5).End(xlUp))
If DateSerial(Year(c.Value), Month(c.Value), 1) = Cells(ligne, 40) Then
If Application.CountIf(Range([E4], c), c) = 1 Then
Set Plage = Range([E4], Cells(Rows.Count, 5).End(xlUp))
Somme = Evaluate("sumif(" & Plage.Address & "," & c.Address & "," & Plage.Offset(, 2).Address & ")")
Moyenne = Evaluate("averageif(" & Plage.Address & "," & c.Address & "," & Plage.Offset(, 3).Address & ")")
If Somme > 0 Then 'Si la somme des valeurs "c" pour la meme date est positive alors
Cells(LigneCred, "AR") = Cells(c.Row, 5)
Cells(LigneCred, "AQ") = Cells(c.Row, 8) 'la moyenne des valeurs de la colonne 8 correspondant à la somme de "c"
Cells(LigneCred, "AP") = Somme 'la somme des valeurs "c"
Ctr = Ctr + Somme
LigneCred = LigneCred + 1
Else
Cells(LigneDeb, "AM") = Cells(c.Row, 5)
Cells(LigneDeb, "AL") = Cells(c.Row, 8) 'la moyenne des valeurs de la colonne 8 correspondant à la somme de "c"
Cells(LigneDeb, "AK") = Somme 'la somme des valeurs "c"
Ctr = Ctr + Somme
LigneDeb = LigneDeb + 1
End If
End If
End If
Next c
Cells(ligne + 1, 40) = Ctr
Cells(ligne + 1, 40).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
ligne = Application.Max(LigneCred, LigneDeb)
Next x
Range([AH1], Cells(Rows.Count, "AH")).ClearContents
End Sub |