Computing and Populating Excel Table With VBA

Question

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

Answer 1

First things first - variable naming - help me. I look at your variables and have no idea what they are, what they are meant to do or how they should be used.

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

1. Worksheets have a CodeName property - View Properties window (F4) and the (Name) field (the one at the top) can be used as the worksheet name. This way you can avoid set wsAssignedTix = Sheets("AssignedTickets") and instead just use AssignedTickets.
2. Integers - integers are obsolete. According to msdn VBA silently converts all integers to long.

Let's try to decipher what your variables are and give them meaningful names

d = priorityValue
ctP1 = countOfCritical?
ctP1RespSLA - countOfCriticalResponseServiceLevelAgreement?

Actually, I can't decipher them. Sorry.

---

For Each d In wsAssignedTix.Range(wsAssignedTix.Range("H1").End(xlDown), wsAssignedTix.Range("H" & Rows.Count).End(xlUp))

This could be done differently:

Dim lastRow As Long
lastRow = wsAssignedTix.Cells(Rows.Count, 8).End(xlUp).Row
Dim i As Long
For i = 1 To lastRow

Now change your if

Const CRITICAL_PRIORITY As String = "Priority 1 Incident - Critical"
Const CLOSED_OR_RESOLVED As String = "Closed Resolved"
If Cells(i, 8) = CRITICAL_PRIORITY And InStr(1, CLOSED_OR_RESOLVED, Cells(i, 7).Value) > 0 Then

Now do whatever all that adding is, but you can avoid the offset by just using the column number with the current row.

I'd also avoid doing the calculation in the With block by using a variable, personally.

---

Without really understanding what you're calculating, and assuming your values are either 1 or 0 you can probably just sum as you go rather than testing each offset cell for 1.

---

And, most likely, you'd benefit from loading the tickets sheet into an array and calculating on the array rather than the sheet.

---

Actually, what you could probably do is sort the tickets by critical and then just sum the columns for each status based on the count of critical tickets. That would be simpler.