MrExcel Publishing
Your One Stop for Excel Tips & Solutions

macro to get sub-totals


Posted by Colin Sullivan on October 05, 2001 9:24 AM

I need to find a macro so that it will search down a specific column for the word Sub-Total and then sum the data in a different column since the last instance of the word sub-total.

I cannot figure this out but anyone who can help please do.

Thanks, Colin


Posted by faster on October 05, 2001 12:09 PM

Option Explicit

Sub SubTotal()
'this should do it but you will have to change three things
'to suit your needs
'run this code on a copy of your worksheet

Dim MyCol
'change 6 to column you want subtotals in
'where A = 1, B = 2, C = 3. . .
MyCol = 7

Dim SubCol
'change 1 to the column number that contains "Sub-Total"
'where A = 1, B = 2, C = 3. . .
SubCol = 1

Dim ValCol
'change 3 to the column number that you want to use for subtotals
'where A = 1, B = 2, C = 3. . .
ValCol = 3

'****************************************
'Don't change anything below this point
'****************************************

Dim MySub
MySub = 0
Cells(1, MyCol).Select
Do While Selection.Row <> ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
If Cells(Selection.Row, SubCol) = "Sub-Total" Then
MySub = MySub + Cells(Selection.Row, ValCol)
Selection = MySub
Selection.Offset(1, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop
End Sub