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

 

[StephenCrump]

Why does START_ROW have a value of 4? and what is Const?

Const is how you declare a constant value, i.e. one that can be fixed up front and will never need to change.

I think I started out thinking the amounts that needed summing started on row 4. But given the SUMIF approach I used, that's not really relevant.

So you could scrap START_ROW and simply use:

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

 

[mtbthepro]

Got it, Thanks again