Macro Subtotals

[Guest]

I created a macro that will sort and subtotal information. I then added a
formula that will calculate the grand totals of all the subtotals and
multiply by a number I indicated above. The problem: If I have more or less
rows of info than I did when I created the macro, my formula is messed up
because the grand total moved up or down a rows while my formula is reading
one particular space. Any way to get the grand totals fixed in one location
regardless of rows the macro is adding?

Thanks!!

 

[Trevor Shuttleworth]

Amber

you have to establish the last row. For example, if you know that column A
has data in the last row, you can use:

Dim LastRow as Long
LastRow = Range("A65536").End(xlUp).Row

to establish the last row with data..

You can then use:

Range("A" & LastRow + 1). Formula = ...

to position your formula. Change the 1 to a 2 if you want a gap before the
subtotal.

When you ask a question like this it is always better to post your code as
you are likely to get a fuller answer.

Regards

Trevor

 

[Guest]

Here is my macro. I'd like the Grand totals to consistently show up on row
126. Will your information still work? Thanks!

Sub Piece()
'
' Piece Macro
' Macro recorded 12/10/2004 by Gayle Morris
'
' Keyboard Shortcut: Ctrl+p
'
Range("A14:K120").Select
Selection.Sort Key1:=Range("A14"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A13:K120").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3,
4, 5, _
6, 7, 8, 9, 11), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=3
Range("B128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("C128").Select
Selection.Style = "Currency"
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("D128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C)"
Range("K129").Select
ActiveWindow.SmallScroll Down:=12
Range("A131").Select
ActiveCell.FormulaR1C1 = "Totals"
Range("B131").Select
ActiveCell.FormulaR1C1 = ""
Range("A131").Select
End Sub

 

[Trevor Shuttleworth]

Amber

maybe this is what you need:

Sub Piece()
Range("A14:K120").Sort _
Key1:=Range("A14"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A13:K120").Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(2, 3, 4, 5, 6, 7, 8, 9, 11), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True
Range("B128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
With Range("C128")
.Style = "Currency"
.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
End With
Range("D128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I128").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K128").FormulaR1C1 = "=SUM(R[-2]C)"
Range("A131").Value = "Totals"
Range("B131").Value = ""
' This puts a pointer to the grand total cell into cell K126
Range("K126").Formula = "=" & _
Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Offset(0, 10).Address
Range("A131").Select
End Sub

Regards

Trevor

Here is my macro. I'd like the Grand totals to consistently show up on row
126. Will your information still work? Thanks!

Sub Piece()
'
' Piece Macro
' Macro recorded 12/10/2004 by Gayle Morris
'
' Keyboard Shortcut: Ctrl+p
'
Range("A14:K120").Select
Selection.Sort Key1:=Range("A14"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A13:K120").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3,
4, 5, _
6, 7, 8, 9, 11), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=3
Range("B128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("C128").Select
Selection.Style = "Currency"
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("D128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K128").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C)"
Range("K129").Select
ActiveWindow.SmallScroll Down:=12
Range("A131").Select
ActiveCell.FormulaR1C1 = "Totals"
Range("B131").Select
ActiveCell.FormulaR1C1 = ""
Range("A131").Select
End Sub

Amber

you have to establish the last row. For example, if you know that column
A
has data in the last row, you can use:

Dim LastRow as Long
LastRow = Range("A65536").End(xlUp).Row

to establish the last row with data..

You can then use:

Range("A" & LastRow + 1). Formula = ...

to position your formula. Change the 1 to a 2 if you want a gap before
the
subtotal.

When you ask a question like this it is always better to post your code
as
you are likely to get a fuller answer.

Regards

Trevor