Hi All, I would like to run the below code four times with four different date ranges, 1. This month, 2. Last month, 3. Last quarter, 4. Previous quarter (the quarter prior to last quarter). I would like them NOT static, but rolling forward with time if possible, cheers.
Code:
Private Sub Count_KPI11()With Sheets("Last Mth Processed")
.Range("E5").Value = CountA("Work", "WSCA1", "Processed")
.Range("E6").Value = CountA("Work", "WSCA2", "Processed")
.Range("E7").Value = CountA("Work", "SECA11", "Processed")
.Range("E8").Value = CountA("Work", "SECA12", "Processed")
.Range("E9").Value = CountA("Work", "SECA13", "Processed")
.Range("E17").Value = CountA("Work", "NWCA5", "Processed")
.Range("E18").Value = CountA("Work", "NWCA6A", "Processed")
.Range("E19").Value = CountA("Work", "NWCA6B", "Processed")
.Range("E20").Value = CountA("Work", "NWCA7", "Processed")
.Range("E21").Value = CountA("Work", "NWCA8", "Processed")
.Range("E22").Value = CountA("Work", "NWCA9", "Processed")
.Range("E23").Value = CountA("Work", "NWCA10A", "Processed")
.Range("E24").Value = CountA("Work", "NWCA10B", "Processed")
.Range("E32").Value = CountA("Work", "WSCA3", "Processed")
.Range("E33").Value = CountA("Work", "WSCA4", "Processed")
.Range("E34").Value = CountA("Work", "SECA14", "Processed")
.Range("E35").Value = CountA("Work", "SECA15", "Processed")
.Range("E36").Value = CountA("Work", "SECA16", "Processed")
End With
End Sub
Function CountA(v1 As String, v2 As String, Optional v3 As String = "") As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range
With Sheets("calculations")
Set rng1 = .Range("D7:D100000")
Set rng2 = .Range("L7:L100000")
Set rng3 = .Range("M7:M100000")
If Len(v3) > 0 Then
CountA = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3)
Else
CountA = Application.CountIfs(rng1, v1, rng2, v2)
End If
End With
End Function
