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
