Insert SUM value on varying row
Posted by Zif on September 13, 2000 6:47 AM
Hiya
I have a macro which summarises data from variuos sources onto an "output" worksheet.
The output can have anywhere between 300 and 5000 rows of data each time I run the macro.
What I'd like to do is to automatically add to th ebottom of each column of data the value of the SUM of the cells above.
The row number on which this total figure will be appearing will of course vary depending on how many rows of data there are.
Any suggestions?
Posted by JAF on September 14, 0100 3:54 AM
Hi
I recently had the same problem where I needed to put the value of the sum of several columns of data on the last row. I came up with...
Sub insert_row_sum_value()
Application.ScreenUpdating = False
Range("A1").Select
Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Static sum_of_range
sum_of_range = Application.WorksheetFunction.Sum(Selection)
ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range
ActiveCell.Offset(0, 1).Select
Loop Until ActiveCell.Offset(0, 0).Value = ""
Range("A1").Select
ActiveCell.End(xlDown).Select
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
Selection.NumberFormat = "$#,##0.00"
Selection.Font.FontStyle = "Bold"
Range("A1").Select
Application.ScreenUpdating = True
End Sub
This inserts the value of the sum of the row and formats it as bold and currency format.
Hope this helps.
Posted by David on September 13, 0100 8:56 AM
substitute your top cell for a1
temp = Range("a1").End(xlDown).Row - 1
Range("a1").End(xlDown).FormulaR1C1 = "=SUM(R[-" & temp & "]C:R[-1]C)"
