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
| Public Sub total_mit()
Dim s As Worksheet
Set s = Worksheets("Statistic")
Dim s1 As Worksheet
Set s1 = Worksheets("Mitigation Actions")
Dim all_mit As Long
Dim nbnop_last As Long
Dim nbpassed_all As Long
nbnop_last = 0
all_mit = 0
nbpassed_all = 0
Dim j As Integer
j = 0
While (Not (IsEmpty(s1.Range("A8").Offset(j))))
'count total mitigation actions
If (Not (IsEmpty(s1.Range("A8").Offset(j)))) Then
all_mit = all_mit + 1
End If
s.Range("b28").Value = all_mit
If s1.Range("i8").Offset(j) = "in progress" Then
nbnop_last = nbnop_last + 1
End If
s.Range("c28").Value = nbnop_last
'count target date passed mitigation actions
If (s1.Range("H8").Offset(j).Value <= s1.Range("B1").Value) And s1.Range("I8").Offset(j) <> "complete" Then
nbpassed_all = nbpassed_all + 1
End If
s.Range("d28").Value = nbpassed_all
'count target date passed mitigation actions from previous year
Dim nb_pass_late As Integer
nb_pass_late = 0
If (s1.Range("H8").Offset(j).Value <= s1.Range("B1").Value) _
And s1.Range("I8").Offset(j) <> "complete" _
And Year.s1.Range("B8").Offset(j) <> Year.s1.Range("B1") Then
nb_pass_late = nb_pass_late + 1
End If
s.Range("e28").Value = nb_pass_late
j = j + 1
Wend
End Sub |
Partager