alisoncleverly
New Member
- Joined
- Feb 20, 2020
- Messages
- 28
- Office Version
- 2013
- Platform
- Windows
So I have a piece of codes using SumIfs to return Quarterly Totals, breakdown by Quarter and Yearly Total, breakdown by year. However, The Yearly Total, Q1 Total,Q1 breakdown, Q2 Total, Q2 breakdowns return correct results while Q3 and Q4 breakdowns all have 0s, even though Q3 and Q4 Totals return correct results.
Here is a snapshot of the template in which the code should return values.
As you can see from the print screen, Q2 results are correctly calculated and located (both breakdown numbers and total). However, for Q3, the total for each category is correct but the breakdowns are all 0s which is absurd since the formulas I used for each Quarter are exactly the same, only the ranges to calculate Quarterly values changed (which I double checked again is correct).
Here is my codes:
I don't know why but Q1, Q2 share the exact same formulas with Q3 and Q4 but Q3, Q4 somehow don't work.
This is on my "Snapshot" worksheet:
Please advise! Any help is highly appreciated! Thank you!
Here is a snapshot of the template in which the code should return values.
As you can see from the print screen, Q2 results are correctly calculated and located (both breakdown numbers and total). However, for Q3, the total for each category is correct but the breakdowns are all 0s which is absurd since the formulas I used for each Quarter are exactly the same, only the ranges to calculate Quarterly values changed (which I double checked again is correct).
Here is my codes:
VBA Code:
Sub UpdateSnapshot()
'Set up Message Box
If MsgBox("Update Snapshot?", vbYesNo + vbQuestion + vbDefaultButton2, "Opportunity Snapshot 2020") = vbNo Then
Exit Sub
End If
'Declare variables
Dim wsOpps As Worksheet, wsSnapshot As Worksheet
Dim r As Integer, c As Integer
Set wsOpps = ThisWorkbook.Sheets("Opps tracker 2020-2021")
Set wsSnapshot = ThisWorkbook.Sheets("Snapshot")
Dim SumRgn As Range 'Total column in Opps worksheet
Dim CrtYrPrime As Range
Dim CrtCat As Range
Dim CrtYrList As Range
Dim CrtRgnPrime As Range
Dim CrtRgnCat As Range
Dim CrtRgnYr As Range
Dim CrtRgnQ1 As Range 'Range Q1
Dim CrtRgnQ2 As Range 'Range Q2
Dim CrtRgnQ3 As Range 'Range Q3
Dim CrtRgnQ4 As Range 'Range Q4
Dim CrtQ1Prime As Range
Dim CrtQ2Prime As Range
Dim CrtQ3Prime As Range
Dim CrtQ4Prime As Range
With wsOpps
Set SumRgn = .Range("T1:T2000") 'Total column in Opps
Set CrtRgnPrime = .Range("C1:C2000") 'Prime Model
Set CrtRgnCat = .Range("K1:K2000") 'Category
Set CrtRgnYr = .Range("J1:J2000") 'Year
Set CrtRgnQ1 = .Range("L1:L2000") 'Quarter 1
Set CrtRgnQ2 = .Range("N1:N2000") 'Quarter 2
Set CrtRgnQ3 = .Range("P1:P2000") 'Quarter 3
Set CrtRgnQ4 = .Range("R1:R2000") 'Quarter 4
End With
With wsSnapshot
Set CrtYrPrime = .Range("$A$3") 'Prime Model for Year x
Set CrtQ1Prime = .Range("$A$22")
Set CrtQ2Prime = .Range("$A$41")
Set CrtQ3Prime = .Range("$A$60")
Set CrtQ4Prime = .Range("$A$79")
Set CrtCat = .Range("$B$1") 'Category
Set CrtYrList = .Range("$A$1") 'Year list
End With
'IMPORTANT -- Turn off events
Application.EnableEvents = False
'Clear old data in Worksheet Snapshot
wsSnapshot.Range("B3:K20, B22:K39, B41:K58, B60:K77, B79:K96").ClearContents
'Yearly breakdown
For r = 3 To 19
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(SumRgn, CrtRgnPrime, CrtYrPrime.Offset(r - 3, 0), _
CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Yearly Total
For r = 20 To 20
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(SumRgn, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q1 breakdown
For r = 22 To 38
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ1, CrtRgnPrime, CrtQ1Prime.Offset(r - 3, 0), _
CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q2 breakdown
For r = 41 To 57
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ2, CrtRgnPrime, CrtQ2Prime.Offset(r - 3, 0), _
CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q3 breakdown
For r = 60 To 76
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ3, CrtRgnPrime, CrtQ3Prime.Offset(r - 3, 0), _
CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q4 breakdown
For r = 79 To 95
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ4, CrtRgnPrime, CrtQ4Prime.Offset(r - 3, 0), _
CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q1 Total
For r = 39 To 39
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ1, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q2 Total
For r = 58 To 58
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ2, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q3 Total
For r = 77 To 77
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ3, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'Q4 Total
For r = 96 To 96
For c = 2 To 11
wsSnapshot.Cells(r, c) _
= Application.WorksheetFunction.SumIfs(CrtRgnQ4, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
Next c
Next r
'IMPORTANT -- Turn on events
Application.EnableEvents = True
End SubI don't know why but Q1, Q2 share the exact same formulas with Q3 and Q4 but Q3, Q4 somehow don't work.
This is on my "Snapshot" worksheet:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call UpdateSnapshot
End If
End SubPlease advise! Any help is highly appreciated! Thank you!
Last edited:
