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
| Sub Calculs()
Dim c As Range, MoisDeb As Integer, Col As Integer, MoisFin As Integer
Dim DateDeb As Date, DateFin As Date
With Sheets("Feuil2")
.Range(.[A3], .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 10).Resize(, 12).ClearContents
For Each c In Range(.[F3], .Cells(.Rows.Count, 6).End(xlUp))
If Year(c.Offset(, 2)) >= 2012 Then
If Year(c.Offset(, 1)) < 2012 Then
MoisDeb = 1
DateDeb = DateSerial(2012, 1, 1)
Else
MoisDeb = Month(c.Offset(, 1))
DateDeb = c.Offset(, 1)
End If
If Year(c.Offset(, 2)) > 2012 Then
MoisFin = 13
DateFin = DateSerial(2012, 12, 31)
Else
MoisFin = Month(c.Offset(, 2))
DateFin = c.Offset(, 2)
End If
For Col = 11 To 22
Select Case MoisDeb
Case Col - 10
If MoisFin = Col - 10 Then
.Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateFin) * c.Offset(, 3) / 100
Else
.Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
End If
Case Is < Col - 10
If MoisFin = Col - 10 Then
.Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateFin) * c.Offset(, 3) / 100
ElseIf MoisFin > Col - 10 Then
.Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
End If
End Select
Next Col
End If
Next c
End With
End Sub |
Partager