# Computing and Populating Excel Table With VBA

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


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.