Finding the sum for a grand total

[mtbthepro]

Hi there,
I have this code which is meant to add two "account totals", each account total is located in two different departments. The macro is then to display the sum of those two "account total" in the row of "grand total" in ColumnD:H. The numbers start in the "Account total" ROW are located from column D to H.

The issue I am having is the macro not showing anything in the Grand total row.

Please help. Thank you in advance.

Sub R_SHT03_FORMAT_Tashana()
Sheets("Tashana").Activate
Set sht = ActiveWorkbook.ActiveSheet

                
nlast = Cells(Rows.Count, "A").End(xlUp).Row                    
For n = nlast To 1 Step -1
                    If sht.Cells(n, 1).Value = "Department 73   Central Reservation" Then
                        If sht.Cells(n, b).Value = "ACCOUNT TOTAL" Then
                                Value1 = Value1 + sht.Cells(n, 4).Value
                                Value2 = Value1 + sht.Cells(n, 5).Value
                                Value3 = Value1 + sht.Cells(n, 6).Value
                                Value4 = Value1 + sht.Cells(n, 7).Value
                                Value5 = Value1 + sht.Cells(n, 8).Value
                        End If
                    End If
                    
                    If sht.Cells(n, 1).Value = "Department 73: GR Central Reservation" Then
                        If sht.Cells(n, 2).Value = "ACCOUNT TOTAL" Then
                                Value1 = Value1 + sht.Cells(n, 4).Value
                                Value2 = Value1 + sht.Cells(n, 5).Value
                                Value3 = Value1 + sht.Cells(n, 6).Value
                                Value4 = Value1 + sht.Cells(n, 7).Value
                                Value5 = Value1 + sht.Cells(n, 8).Value
                        End If
                    End If
  
                    If sht.Cells(n, 3).Value = "GRAND TOTAL" Then
                                sht.Cells(n, 4).Value = Value1
                                sht.Cells(n, 5).Value = Value2
                                sht.Cells(n, 6).Value = Value3
                                sht.Cells(n, 7).Value = Value4
                                sht.Cells(n, 8).Value = Value5
                        End If
              Next n
End If

 

[mtbthepro]

Edited code, without errors but still does not works.

Sub R_SHT03_FORMAT_Tashana()Sheets("Tashana").Activate
Set sht = ActiveWorkbook.ActiveSheet


                
             nlast = Cells(Rows.Count, "A").End(xlUp).Row
                    For n = nlast To 1 Step -1
                    If sht.Cells(n, 1).Value = "Department 73   Central Reservation" Then
                        If sht.Cells(n, 2).Value = "ACCOUNT TOTAL" Then
                                Value1 = Value1 + sht.Cells(n, 4).Value
                                Value2 = Value2 + sht.Cells(n, 5).Value
                                Value3 = Value3 + sht.Cells(n, 6).Value
                                Value4 = Value4 + sht.Cells(n, 7).Value
                                Value5 = Value5 + sht.Cells(n, 8).Value
                        End If
                    End If
                    
                    If sht.Cells(n, 1).Value = "Department 73: GR Central Reservation" Then
                        If sht.Cells(n, 2).Value = "ACCOUNT TOTAL" Then
                                Value1 = Value1 + sht.Cells(n, 4).Value
                                Value2 = Value2 + sht.Cells(n, 5).Value
                                Value3 = Value3 + sht.Cells(n, 6).Value
                                Value4 = Value4 + sht.Cells(n, 7).Value
                                Value5 = Value5 + sht.Cells(n, 8).Value
                        End If
                    End If
  
                    If sht.Cells(n, 3).Value = "GRAND TOTAL" Then
                                sht.Cells(n, 4).Value = Value1
                                sht.Cells(n, 5).Value = Value2
                                sht.Cells(n, 6).Value = Value3
                                sht.Cells(n, 7).Value = Value4
                                sht.Cells(n, 8).Value = Value5
                        End If
                Next n

 

[StephenCrump]

Can you post a small sample of your Tashana worksheet? There are many possible reasons your totals might be calculating as zero, but without seeing your data layout we'll only be guessing.

(I also suspect we'll also be able to provide a much better solution, e.g. using Excel formulae rather than having VBA loop through every row and return the totals as static values).

 

[mtbthepro]

Yes Of course, The sheet looks like this

Department 73 Central Reservation
ACCOUNT#: 1-025-73-00000-3651
MEMBER NUMBER	MEMBER NAME	TRN DATE	0 -30 DAYS	31-60 DAYS	61-90 DAYS	OVER 90 DAYS	PAST DUE
073-500001120-00	Creative Tours US	1/9/2017	$0.00	$0.00	$0.00	$525.00	$525.00
073-500006794-00	Dalhoff Travel 20	11/21/2016	$0.00	$0.00	$0.00	$784.00	$784.00
073-500006794-00	Dalhoff Travel 20	11/21/2016	$0.00	$0.00	$0.00	$1,078.00	$1,078.00
073-500006794-00	Dalhoff Travel 20	11/21/2016	$0.00	$0.00	$0.00	$462.00	$462.00
073-500002487-00	Dwight School	1/7/2017	$0.00	$0.00	$0.00	$295.00	$295.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/3/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
073-500015014-00	Eur Au Pair 2017	8/29/2016	$0.00	$0.00	$0.00	$2,380.00	$2,380.00
026-500067920-00	Indiana Univ. of	1/27/2017	$0.00	$0.00	$0.00	$323.00	$323.00
073-500015037-00	Jysk rejsebureau	3/9/2017	$0.00	$0.00	$0.00	$3,136.00	$3,136.00
073-500015034-00	Les Adventures Ma	1/23/2017	$0.00	$0.00	$0.00	$533.00	$533.00
073-500015034-00	Les Adventures Ma	3/13/2017	$0.00	$0.00	$0.00	$675.00	$675.00
073-500015034-00	Les Adventures Ma	3/30/2017	$0.00	$0.00	$0.00	$680.00	$680.00
026-500002334-00	New World Travel	2/23/2017	$0.00	$0.00	$0.00	$336.00	$336.00
073-500015038-00	Skibound Ltd Trad	3/9/2017	$0.00	$0.00	$0.00	$487.00	$487.00
073-500001356-00	St. John's Cathed	3/8/2017	$0.00	$0.00	$0.00	$761.00	$761.00
073-500006172-00	STA TRAVEL GROUP	12/14/2016	$0.00	$0.00	$0.00	$24.00	$24.00
073-500006172-00	STA TRAVEL GROUP	3/2/2017	$0.00	$0.00	$0.00	$406.00	$406.00
073-500001980-00	Unique Reisen	12/27/2016	$0.00	$0.00	$0.00	$436.00	$436.00
073-500000535-00	University of Mic	2/14/2017	$0.00	$0.00	$0.00	$147.00	$147.00
090-500000294-00	Vip Voyages	12/28/2016	$0.00	$0.00	$0.00	$295.00	$295.00
	ACCOUNT TOTAL		$0.00	$0.00	$0.00	$32,803.00	$32,803.00
Department 73: GR Central Reservation
MEMBER NAME	MEMBER ROOM	TRN DATE	0-30 DAYS	31-60 DAYS	61-90 DAYS	OVER 90 DAYS	PAST DUE
Akamatsu, Naoki	826	02/20/2017 02/23/2017	$0.00	$276.00	$0.00	$0.00	$276.00
Aldridge, Mavis	1163	03/23/2017 03/28/2017	$368.00	$0.00	$0.00	$0.00	$368.00
Andriasov, Arshak	805	04/01/2017 04/02/2017	$112.91	$0.00	$0.00	$0.00	$112.91
Barron, Valentina Aquirre	1217	03/31/2017 04/01/2017	$83.64	$0.00	$0.00	$0.00	$83.64
Bekker Sundgaard, Nicolai	854	04/03/2017 04/10/2017	$224.00	$0.00	$0.00	$0.00	$224.00
Bellinello, Giacomo	1236	03/05/2017 04/04/2017	$3,240.00	$0.00	$0.00	$0.00	$3,240.00
Bolina, Guilherme	843	01/22/2017 02/19/2017	$0.00	$0.00	$2,240.00	$0.00	$2,240.00
Carr Hill, NST	1140	02/15/2017 02/19/2017	$0.00	$360.00	$0.00	$0.00	$360.00
Diekmann, Markus	1055	03/18/2017 04/08/2017	$1,350.12	$0.00	$0.00	$0.00	$1,350.12
Duncan, Kattrin	1255	04/01/2017 04/02/2017	$101.11	$0.00	$0.00	$0.00	$101.11
Durrant, Ryan	851	04/03/2017 04/05/2017	$224.00	$0.00	$0.00	$0.00	$224.00
Fele, Giuseppe	1351	03/28/2017 04/02/2017	$480.00	$0.00	$0.00	$0.00	$480.00
Fujita, Ryohei	1173	03/03/2017 03/09/2017	$0.00	$469.20	$0.00	$0.00	$469.20
Halgrener, Johannes	1006	04/01/2017 04/04/2017	$336.00	$0.00	$0.00	$0.00	$336.00
Hanada, Takahiro	1109	02/25/2017 03/18/2017	$0.00	$1,659.00	$0.00	$0.00	$1,659.00
Hara, Shuhei	1133	03/10/2017 03/15/2017	$391.00	$0.00	$0.00	$0.00	$391.00
Irague, Florian	830	04/01/2017 04/10/2017	$380.00	$0.00	$0.00	$0.00	$380.00
Ishikura, Hironori	833	03/16/2017 03/19/2017	$234.60	$0.00	$0.00	$0.00	$234.60
Ishizuka, Yuki	1034	03/31/2017 04/05/2017	$540.65	$0.00	$0.00	$0.00	$540.65
Israel, Thomas	802	03/31/2017 04/01/2017	$77.37	$0.00	$0.00	$0.00	$77.37
Jansen, Nadine	841	04/03/2017 04/07/2017	$190.00	$0.00	$0.00	$0.00	$190.00
Jokela, Miikka	1216	04/02/2017 04/09/2017	$255.99	$0.00	$0.00	$0.00	$255.99
Jysk rejsebureau	810	03/31/2017 04/05/2017	$15.98	$0.00	$0.00	$0.00	$15.98
Kaburagi, Tsutomu	841	03/31/2017 04/03/2017	$234.60	$0.00	$0.00	$0.00	$234.60
Kappler, Isabell	1045	04/02/2017 04/08/2017	$336.00	$0.00	$0.00	$0.00	$336.00
Liou, RueiTzung	1329	04/01/2017 04/08/2017	$358.40	$0.00	$0.00	$0.00	$358.40
Masukawa, Airi	1140	03/18/2017 03/21/2017	$234.60	$0.00	$0.00	$0.00	$234.60
Matsuda, Yuya	826	03/16/2017 03/21/2017	$391.00	$0.00	$0.00	$0.00	$391.00
Matsumoto, Kosuke	823	03/16/2017 03/20/2017	$312.80	$0.00	$0.00	$0.00	$312.80
Matsuo, Nanae	1074	03/17/2017 03/21/2017	$312.80	$0.00	$0.00	$0.00	$312.80
Meuer, Katharina	860	04/03/2017 04/09/2017	$224.00	$0.00	$0.00	$0.00	$224.00
Molina, Yanina Victoria	833	03/30/2017 04/02/2017	$279.00	$0.00	$0.00	$0.00	$279.00
Musarra, Fernando	1033	04/04/2017 04/10/2017	$84.34	$0.00	$0.00	$0.00	$84.34
Nagatomo, Satomi	822	02/18/2017 02/23/2017	$0.00	$391.00	$0.00	$0.00	$391.00
Nakayama, Jun	1064	03/22/2017 03/26/2017	$312.80	$0.00	$0.00	$0.00	$312.80
Ohba, Chie	1255	03/31/2017 04/01/2017	$88.52	$0.00	$0.00	$0.00	$88.52
Omura, Yuki	1014	04/02/2017 04/07/2017	$336.00	$0.00	$0.00	$0.00	$336.00
Quaas, Josephine	825	03/06/2017 03/11/2017	$463.25	$0.00	$0.00	$0.00	$463.25
Ramirez, Herlinda	1136	03/15/2017 03/19/2017	$312.80	$0.00	$0.00	$0.00	$312.80
Rangasamy, Kannan	1247	04/02/2017 04/07/2017	$258.03	$0.00	$0.00	$0.00	$258.03
Ravichandran, Vignesh	1207	04/02/2017 04/07/2017	$258.03	$0.00	$0.00	$0.00	$258.03
Rischar, Kerstin	858	04/03/2017 04/08/2017	$224.00	$0.00	$0.00	$0.00	$224.00
Rodriguez, Alfred	1255	04/02/2017 05/01/2017	$336.00	$0.00	$0.00	$0.00	$336.00
Rohne, Helena	1020	03/23/2017 03/30/2017	$547.40	$0.00	$0.00	$0.00	$547.40
Romano, Nicholas	1202	04/03/2017 04/04/2017	$99.63	$0.00	$0.00	$0.00	$99.63
Ruterschmidt, Maximilian	1053	04/01/2017 04/10/2017	$304.00	$0.00	$0.00	$0.00	$304.00
Saari, Jill	1238	03/31/2017 04/01/2017	$83.64	$0.00	$0.00	$0.00	$83.64
Sato, Yukiko	831	03/20/2017 03/23/2017	$234.60	$0.00	$0.00	$0.00	$234.60
Schmitt, Sabrina	814	03/15/2017 03/17/2017	$184.00	$0.00	$0.00	$0.00	$184.00
Shanker, Daya	843	03/31/2017 04/01/2017	$68.65	$0.00	$0.00	$0.00	$68.65
Shimizu, Takashi	1053	03/16/2017 03/25/2017	$703.80	$0.00	$0.00	$0.00	$703.80
Suzuki, Momo	1138	03/14/2017 03/20/2017	$469.20	$0.00	$0.00	$0.00	$469.20
Tagliani Tavares, Camila	1308	03/05/2017 04/01/2017	$2,133.00	$0.00	$0.00	$0.00	$2,133.00
Toscano, Alejandro	849	03/31/2017 04/03/2017	$282.00	$0.00	$0.00	$0.00	$282.00
Trammell, Jonathan S	1133	03/31/2017 04/01/2017	$77.37	$0.00	$0.00	$0.00	$77.37
Travelbound 2015 West Side	830	02/05/2017 02/08/2017	$0.00	$102.00	$0.00	$0.00	$102.00
Trotter, Ian	1063	03/27/2017 04/01/2017	$391.00	$0.00	$0.00	$0.00	$391.00
Tsugamura, Marika	1171	03/15/2017 03/18/2017	$234.60	$0.00	$0.00	$0.00	$234.60
Valdez, Mark	823	03/31/2017 04/01/2017	$83.64	$0.00	$0.00	$0.00	$83.64
Velazquez, Francisco Tomas	1256	04/01/2017 04/05/2017	$448.00	$0.00	$0.00	$0.00	$448.00
Wessolowskistroemer, Katinka	1145	03/11/2017 03/18/2017	$763.00	$0.00	$0.00	$0.00	$763.00
Willemsen, Anna	843	02/24/2017 03/18/2017	$0.00	$1,720.40	$0.00	$0.00	$1,720.40
Yakoubi, Luke	855	04/03/2017 04/09/2017	$179.20	$0.00	$0.00	$0.00	$179.20
Yasunaga, Tomohiro	1203	03/30/2017 04/03/2017	$370.60	$0.00	$0.00	$0.00	$370.60
Yayama, Rio	812	03/29/2017 04/01/2017	$277.95	$0.00	$0.00	$0.00	$277.95
	ACCOUNT TOTAL		$21,897.62	$4,977.60	$2,240.00	$0.00	$29,115.22
		GRAND TOTAL

<tbody>
</tbody>

 

[StephenCrump]

The reason your code isn't calculating totals is that it is looking for "Department 73 Central Reservation" and "Account Total" on the same row, and never finding it.

There are any number of ways you could code this. Here's just one, using SUMIF formulas:

Sub Test()

    Dim r As Range
    Dim lRow As Long
    Const START_ROW = 4
    
    Set r = Columns("C").Find(What:="GRAND TOTAL", LookIn:=xlValues)
    
    If r Is Nothing Then
        MsgBox "No Grand total line!"
    Else
        lRow = r.Row
        Range("D" & lRow & ":H" & lRow).Formula = "=SUMIF($B" & START_ROW & ":$B" & lRow - 1 _
            & ",""Account Total"",D" & START_ROW & ":D" & lRow - 1 & ")"
    End If

End Sub

Another way would be to use Excel's SUBTOTAL function for each Account Total, and for the Grand Total.

 

[mtbthepro]

Woah, that is works great. One thing is it possible to apply that to all sheet?

 

[StephenCrump]

This will do every worksheet in the workbook, assuming the same layout in each. You can limit this if you like, e.g. to exclude particular worksheets, or to include only certain specified worksheets.

Sub Test()

    Dim ws As Worksheet
    Dim r As Range
    Dim lRow As Long
    Const START_ROW = 4
    
    For Each ws In Worksheets
        With ws
            Set r = .Columns("C").Find(What:="Grand Total", LookIn:=xlValues, MatchCase:=False)
            If r Is Nothing Then
                MsgBox "No Grand total line in " & ws.Name & "!"
            Else
                lRow = r.Row
                .Range("D" & lRow & ":H" & lRow).Formula = "=SUMIF($B" & START_ROW & ":$B" & lRow - 1 _
                    & ",""Account Total"",D" & START_ROW & ":D" & lRow - 1 & ")"
            End If
        End With
    Next ws
    
End Sub

 

[mtbthepro]

Hi there, Thank you again. The code works great. I just have question about the code, Can you please explain what is going on this line...

.Range("D" & lRow & ":H" & lRow).Formula = "=SUMIF($B" & START_ROW & ":$B" & lRow - 1 _
& ",""Account Total"",D" & START_ROW & ":D" & lRow - 1 & ")"

I understand this .Range("D" & lRow & ":H" & lRow) but everything after that isn't making sense to me...

 

[StephenCrump]

With START_ROW=4, and lRow =100 (say) then:

Range("D" & lrow & ":H" & lrow).Formula = "=SUMIF($B" & START_ROW & ":$B" & lrow - 1 _
                    & ",""Account Total"",D" & START_ROW & ":D" & lrow - 1 & ")"

'---->

Range("D100:H100").Formula = "=SUMIF($B4:$B99,"Account Total",D4:D99)"

When you use VBA to put a formula into a range, you just need to specify the formula for the top left hand cell in the range.
Here: =SUMIF($B4:$B99,"Account Total",D4:D99) is the formula we want in cell D100.

VBA will apply the formula across the entire range, adjusting any relative row and column references. It's a bit like the way you'd do it in Excel: get the formula right in the first cell, then copy down and across.

 

[mtbthepro]

Why does START_ROW have a value of 4? and what is Const?
That makes sense but I still haven't gotten a full grasp of it yet, I will just have to practice.
Thank you for helping out anyways. Appreciate it a bunch.