I am working in VBA in Excel from 365. I have a workbook with sheets for several departments, all of which are identical except for the data. The sheet contains a list of items and several columns relating to those items. The data entry always starts at the same row (9) but because of processes elsewhere the lists vary in length so the row containing totals varies from sheet to sheet. I can loop through the sheets and use use a variable, LastRow, to identify the totals row in each sheet. What I want to achieve is a totals cell which automatically recalculates when the data in the column above it changes. When I introduce variables into the SUM function formula the totals stubbornly refuse to recalculate. My code is:
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range(Cells(LastRow, 1), Cells(lRow, 10))
For Each cel In r
y = cel.Column
Cells(LastRow, y) = WorksheetFunction.Sum(Range(Cells(9, y), Cells(LastRow - 1, y)))
If Cells(LastRow, y) = 0 Then Cells(lRow, y) = ""
Next cel
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range(Cells(LastRow, 1), Cells(lRow, 10))
For Each cel In r
y = cel.Column
Cells(LastRow, y) = WorksheetFunction.Sum(Range(Cells(9, y), Cells(LastRow - 1, y)))
If Cells(LastRow, y) = 0 Then Cells(lRow, y) = ""
Next cel
staffy.xlsb | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Department A | |||||||||||
2 | ||||||||||||
3 | Production Activity Summary | |||||||||||
4 | ||||||||||||
5 | ||||||||||||
6 | Activity Description | Activity Repeats | Staff | Activity Length Hours | Cost | |||||||
7 | A | B | C | |||||||||
8 | Person 1 | Person 2 | Person 3 | |||||||||
9 | Activity one | 15 | 15 | 2.00 | 30.00 | |||||||
10 | Activity two | 5 | 3 | 2 | 2.00 | 10.00 | ||||||
11 | Activity three | 9 | 4 | 5 | 8.00 | 72.00 | ||||||
12 | Activity four | 17 | 1 | 4 | 12 | 1.00 | 17.00 | |||||
13 | Activity five | 9 | 1 | 3 | 5 | 16.00 | 144.00 | |||||
14 | Totals | |||||||||||
Dept A |