More Grand Totals

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

Guest

I need the Grand Total to always show up on B126 through K126 but it will
only put a grand total in K126. I see in my macro why, but I dont know how to
correctly fix it. Help! :)


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 = ""
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
 
Is this where you tell it to put it in 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


If so, where do you tell it to put similar formulas in B126 to J126?

You would need similar code to put Grand total in the other columns.
 
Or perhaps you could do this

set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
rng.offset(0,1).Resize(1,10).copy
Range("B126").Pastespecial(xlValues)
 
That's the whole problem... This part of the macro was given to me off of
this message board but doesn't work for all the grand total line. I will try
the macro in your next post.

Tom Ogilvy said:
Is this where you tell it to put it in 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


If so, where do you tell it to put similar formulas in B126 to J126?

You would need similar code to put Grand total in the other columns.

--
Regards,
Tom Ogilvy

Amber M said:
I need the Grand Total to always show up on B126 through K126 but it will
only put a grand total in K126. I see in my macro why, but I dont know how to
correctly fix it. Help! :)


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 = ""
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
Click to expand...
Click to expand...
 
I've manipulated the original macro a bit. The grand total is showing up on
row 126 but another grand total is showing up on various rows depending upon
how much data I put in the temp. I want to delete the other grand total... I
only want it on row 126 (columns b-k). Here's my macro, just in case. What
can i do to fix this?! Thanks.


Range("A16:K120").SOrt _
Key1:=Range("A16"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A15: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("D126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K126").FormulaR1C1 = "=SUM(R[-2]C)"
Range("A128").Value = "Totals"
Range("B128").Value = ""
Range("B126").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
Set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
rng.Offset(0, 1).Resize(1, 10).Copy
Range("B126").PasteSpecial (xlValues)
End Sub

Tom Ogilvy said:
Or perhaps you could do this

set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
rng.offset(0,1).Resize(1,10).copy
Range("B126").Pastespecial(xlValues)

--
Regards,
Tom Ogilvy

Amber M said:
I need the Grand Total to always show up on B126 through K126 but it will
only put a grand total in K126. I see in my macro why, but I dont know how to
correctly fix it. Help! :)


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 = ""
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
Click to expand...
Click to expand...
 
Range("A16:K120").SOrt _
Key1:=Range("A16"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A15: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("D126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K126").FormulaR1C1 = "=SUM(R[-2]C)"
Range("A128").Value = "Totals"
Range("B128").Value = ""
Range("B126").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
Set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
if rng.row <> 126 then
rng.Offset(0, 1).Resize(1, 10).Copy
Range("B126").PasteSpecial (xlValues)
rows(rng.row).EntireRow.ClearContents
End if
End Sub

Always test new code on a copy of your data.
--
Regards,
Tom Ogilvy

Amber M said:
I've manipulated the original macro a bit. The grand total is showing up on
row 126 but another grand total is showing up on various rows depending upon
how much data I put in the temp. I want to delete the other grand total... I
only want it on row 126 (columns b-k). Here's my macro, just in case. What
can i do to fix this?! Thanks.


Range("A16:K120").SOrt _
Key1:=Range("A16"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A15: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("D126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("E126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("F126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("G126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("H126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("I126").FormulaR1C1 = "=SUM(R[-2]C*R[-116]C)"
Range("K126").FormulaR1C1 = "=SUM(R[-2]C)"
Range("A128").Value = "Totals"
Range("B128").Value = ""
Range("B126").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
Set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
rng.Offset(0, 1).Resize(1, 10).Copy
Range("B126").PasteSpecial (xlValues)
End Sub

Tom Ogilvy said:
Or perhaps you could do this

set rng = Cells.Find( _
What:="Grand Total", _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
rng.offset(0,1).Resize(1,10).copy
Range("B126").Pastespecial(xlValues)

--
Regards,
Tom Ogilvy

Amber M said:
I need the Grand Total to always show up on B126 through K126 but it will
only put a grand total in K126. I see in my macro why, but I dont know
Click to expand...
how
to
correctly fix it. Help! :)


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 = ""
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top