I am trying to populate a table like this in Excel. I came up with a bunch of loops to do this. I feel like this is a brute-force approach and may not work well when populating bigger tables.
Any other ways to populate values in Excel?
Sub Fill_SLA_priority()
Dim SLAbyPrior As Worksheet
Dim wsAssignedTix As Worksheet
Dim ctP1 As Integer
Dim ctP1RespSLA As Integer
Dim ctP1PlanSLA As Integer
Dim ctP1ResSLA As Integer
Dim ctP1All3SLA As Integer
Dim d As Range
Set SLAbyPrior = Worksheets("SLA by Priority")
Set wsAssignedTix = Worksheets("AssignedTickets")
For Each d In wsAssignedTix.Range(wsAssignedTix.Range("H1").End(xlDown), wsAssignedTix.Range("H" & Rows.count).End(xlUp))
'' Loop through entire list to count number of P1 - P4, Request.
'' P1
'' Count number of Responded, Planned, Resolved, Overall SLA
If d.Value = "Priority 1 Incident - Critical" _
And (d.Offset(0, -1) = "Closed" Or d.Offset(0, -1) = "Resolved") Then
ctP1 = ctP1 + 1
If d.Offset(0, 2) = 1 Then ctP1RespSLA = ctP1RespSLA + 1
If d.Offset(0, 4) = 1 Then ctP1PlanSLA = ctP1PlanSLA + 1
If d.Offset(0, 6) = 1 Then ctP1ResSLA = ctP1ResSLA + 1
If d.Offset(0, 2) = 1 And d.Offset(0, 4) = 1 And d.Offset(0, 6) = 1 Then
ctP1All3SLA = ctP1All3SLA + 1
End If
End If
Next d
'' Populating the cells in SLA_by_Priority
With SLAbyPrior
'' P1 - Resp SLA %
.Range("B5") = ctP1RespSLA / ctP1
'' P1 - Plan SLA %
.Range("C5") = ctP1PlanSLA / ctP1
'' P1 - Resl SLA %
.Range("D5") = ctP1ResSLA / ctP1
'' P1 - All 3 SLA %
.Range("E5") = ctP1All3SLA / ctP1
End With
End Sub