More Grand Totals

A

Amber M

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
 
T

Tom Ogilvy

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.
 
T

Tom Ogilvy

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)
 
A

Amber M

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...
 
A

Amber M

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...
 
T

Tom Ogilvy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro subtotals fixed on one row 2
Totals An Easier Way?? 5
Running Macro on Multiple Worksheets 6
I'm trying to do a "count" function at the end of a dynamic pivot table 0
Autofill is overwriting the column title..... 4
Auto-calculate after a cell is filled in - Macro is looping!!! 2
Auto-calculate after a cell is filled in - macro shouldn't be loop 5
Can't get data centred across pairs of cells...? 2

Top