Macro Subtotals

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!
 
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
 
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

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


Amber M said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top