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 TestCalculTotal()
MsgBox CalculTotal(Range(Cells(2, 3), Cells(13, 3)), Cells(20, 3), Cells(21, 3))
End Sub
Function CalculTotal(Pl As Range, DateM As Date, DateV As Date) As Currency
Dim Cell As Range
Dim RowNo As Long
Dim Mois As Integer, MoisM As Integer, MoisV As Integer
Dim RatioM, RatioV As Currency
Dim Annee As Integer
MoisM = Format(DateM, "m")
MoisV = Format(DateV, "m")
RatioM = (DaysInMonth(Month(DateM), Year(DateM)) - Day(DateM)) / DaysInMonth(Month(DateM), Year(DateM))
RatioV = (Day(DateV)) / DaysInMonth(Month(DateV), Year(DateV))
CalculTotal = 0
For Annee = Year(DateM) To Year(DateV)
For Each Cell In Pl
RowNo = Cell.Row
Mois = Cells(RowNo, 1)
If Mois >= MoisM And Mois <= MoisV Then
CalculTotal = CalculTotal + Cells(RowNo, Pl.Column) * RatioV * RatioM
RatioM = 1
RatioV = 1
End If
Next
Next Annee
End Function
Public Function DaysInMonth(ByVal nMonth As Integer, ByVal nYear As Integer) As Integer
DaysInMonth = Day(DateAdd("d", -1, DateAdd("m", 1, DateSerial(nYear, nMonth, 1))))
End Function |
Partager