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 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
| Sub Macro6()
Dim i, s, t As Integer
Sheets("Feuil_calc_budg").Select
Range("A2").Select
Selection.End(xlDown).Select
ligne_active_cachée = ActiveCell.Row
For i = 3 To ligne_active_cachée
s = 0
If Cells(i, 1) = "Activity" Then
If Cells(i, 8) < Cells(1, 10) Then
s = 0
Else
If Cells(i, 6) < Cells(1, 10) And Cells(i, 8) >= Cells(1, 10) And Cells(i, 8) <= DateAdd("d", 5, Cells(1, 10)) Then
s = [NetworkDays(Cells(1, 10), Cells(i, 8))] * Cells(i, 3)
Else
If Cells(i, 6) < Cells(1, 10) And Cells(i, 8) >= Cells(1, 10) + 5 Then
s = [NetworkDays(Cells(1, 10), DateAdd("d", 5, Cells(1, 10)))] * Cells(i, 3)
Else
If Cells(i, 6) >= Cells(1, 10) And Cells(i, 6) <= DateAdd("d", 5, Cells(1, 10)) And Cells(i, 8) > DateAdd("d", 5, Cells(1, 10)) Then
s = [NetworkDays(Cells(i, 6), DateAdd("d", 5, Cells(1, 10)))] * Cells(i, 3)
Else
If Cells(i, 6) > Cells(1, 10) + 5 Then
s = 0
Else
If Cells(i, 6) >= Cells(1, 10) And Cells(i, 8) <= DateAdd("d", 5, Cells(1, 10)) Then
s = [NetworkDays(Cells(i, 6), Cells(i, 8))] * Cells(i, 3)
Else
s = 0
End If
End If
End If
End If
End If
End If
End If
t = t + s
Next
Range("J2") = t
End Sub
Sub Calculs()
Dim C As Range, s As Integer, t As Integer, Ligne As Long
With Sheets("Feuil_calc_budg")
Ligne = .Cells(.Rows.Count, 3).End(xlUp).Row
For Each C In .Range("J3:J" & Ligne)
s = 0
C.Value = 0
If .Cells(C.Row, 1) = "Activity" Then
If .Cells(C.Row, 8) < .Cells(1, C.Column) Then
Else
If .Cells(C.Row, 6) < .Cells(1, C.Column) And .Cells(C.Row, 8) >= .Cells(1, C.Column) And _
.Cells(C.Row, 8) <= .Cells(1, C.Column) + 5 Then
s = WorksheetFunction.NetworkDays(.Cells(1, C.Column), .Cells(C.Row, 8), [Holidays!$A$6:$A$150]) * .Cells(C.Row, 3)
Else
If .Cells(C.Row, 6) < .Cells(1, C.Column) And .Cells(C.Row, 6) > .Cells(1, C.Column) + 5 Then
s = WorksheetFunction.NetworkDays(.Cells(1, C.Column), .Cells(1, C.Column) + 5, [Holidays!$A$6:$A$150]) * .Cells(C.Row, 3)
Else
If .Cells(C.Row, 6) >= .Cells(1, C.Column) And .Cells(C.Row, 6) <= .Cells(1, C.Column) + 5 And _
.Cells(C.Row, 8) > .Cells(1, C.Column) Then
s = WorksheetFunction.NetworkDays(.Cells(C.Row, 6), .Cells(1, C.Column) + 5, [Holidays!$A$6:$A$150]) * .Cells(C.Row, 3)
Else
If .Cells(C.Row, 6) > .Cells(1, C.Column) + 5 Then
Else
If .Cells(C.Row, 6) >= .Cells(1, C.Column) And .Cells(C.Row, 8) <= .Cells(1, C.Column) + 5 Then
s = WorksheetFunction.NetworkDays(.Cells(C.Row, 6), .Cells(C.Row, 8), _
[Holidays!$A$6:$A$150]) * .Cells(C.Row, 3)
End If
End If
End If
End If
End If
End If
End If
t = t + s
Next C
Range("J2") = t
End With
End Sub |
Partager