Finding the sum for a grand total

M

mtbthepro

Board Regular
Joined
Feb 22, 2017
Messages
91
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.

Code: 
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
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Edited code, without errors but still does not works.
Code: 
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
 
Upvote 0
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).
 
Upvote 0
Yes Of course, The sheet looks like this

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



 
Upvote 0
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.

Code: 
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
 
Upvote 0
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...
 
Upvote 0
With START_ROW=4, and lRow =100 (say) then:

Code: 
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Question Question
Please test this code to check if a variable number of columns are all equal
Replies
2
Views
159
JenniferMurphy
JenniferMurphy
D
  • Question Question
find populated cells in separate region
Replies
1
Views
341
dappy
D
T
  • Solved
Normalizing a Worksheet Output
Replies
2
Views
271
Thomas Scott
T
C
  • Solved
VBA Code to move Amount Due from individual sheets to a master sheet, and then copy information from the master sheet back to the individual sheets?
Replies
7
Views
377
Coyotex3
C
J
  • Solved
VBA to merge duplicate rows and sum values in certain column with adjustments
Replies
9
Views
853
Alex Blakenburg
A

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top