Sum
Posted by Andonny on December 14, 2000 3:48 AM
Hi,
I was wondering if I could get help with the following: (Sample is at http://www.multiline.com.au/~wje/macro2.html )
I have a set of data given to me and I have to SUM the individual items as a total as per sample (Figures in red). There needs to be a new row where the totals are and a new empty row after that. On top of all this I never know how many rows of emu or chicken or beef there will be every time I get the report. I would love to have a macro which could do this for me.
Thank you for your help in advance
Andonny
Posted by Celia on December 15, 2000 1:22 AM
Sub Insert_Rows_And_Sum()
Dim cell As Range, checkVal As String
Dim topRow%, qtyCol%, totCol%
Set cell = Range("C4")
qtyCol = 4
totCol = 6
checkVal = cell.Value
topRow = cell.Row
Application.ScreenUpdating = False
Do Until IsEmpty(cell) And IsEmpty(cell.Offset(-2, 0))
If cell.Value <> checkVal Then
Range(cell, cell.Offset(1, 0)).EntireRow.Insert
Cells(cell.Row - 2, qtyCol).Formula = _
"=Sum(" & Range(Cells(topRow, qtyCol), _
Cells(cell.Row - 3, qtyCol)).Address(False, False) & ")"
Cells(cell.Row - 2, totCol).Formula = _
"=Sum(" & Range(Cells(topRow, totCol), _
Cells(cell.Row - 3, totCol)).Address(False, False) & ")"
checkVal = cell.Value
topRow = cell.Row
Else
Set cell = cell.Offset(1, 0)
End If
Loop
End Sub
Posted by Celia on December 15, 2000 1:30 AM
Post Script
Or perhaps just using Excel's Sub-Total tool will be sufficient.
Celia
