I Need To Automate Totals

[calihiker]

Hi, I'm trying to figure out if it is possible by using a macro/vba to sum a list of contiguous cells in column B by determining if the value in column A = 'Total'. I know I can do something like IF(A10 = "Total", Sum(B1:B9)), but the problem is I don't know automatically how many rows will need to be summed for that group. I can't use subtotals because I need to preserve the existing formatting and there are other subtotals that I need to leave along. Is there a way by using a macro/vba to determine how many rows need to be totalled in each contiuous group of rows, seperated my blanks (2 or more blank rows divide each group).

Thanks for your help,
Andre

 

[SkipVought]

Hi,

Try this code...

Sub SumTotals()
    lPrev = 0
    For Each r In Range(Cells(1, 1), Cells(1, 1).End(xlDown))
       If r.Value = "Total" Then
          If lPrev = 0 Then
             Set rng = Range(Cells(1, 2), Cells(r.Row - 1, 2))
          Else
             Set rng = Range(Cells(lPrev, 2), Cells(r.Row - 1, 2))
          End If
          Cells(r.Row, 3).Value = Application.Sum(rng)
          lPrev = r.Row
       End If
    Next
End Sub

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com