Moving a sum to top

[TonyATS]

I have a sheet with hundreds of cells in a column, sometimes over 700.
I have a VBA sub that sums the values in the cells:

Sub AddRainColumn()
Sheets("Rain").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-991]C:R[-1]C)"
End Sub

In order seen the sum I have to go all the way to the last cell in the column.
I need a way to sum the cells and put the sum at the top of the sheet.
Any idea how I can do this?
Thanks.

[oeldere]

I am no VBA expert but you could try this one.

Range("B1").FormulaR1C1 = "=SUM(R[-991]C:R[-1]C)"

[JEC.]

You don't need VBA for this. Just paste the sum function at the top instead.
That is what you are doing after all

[carlmon]

Please Login or Register  to view this content.

change "B1" to where you want the results, and change the range (D2:D) to whatever column you want

[carlmon]

lol yeah, my code just puts the formula in a cell, it's not really vba

[carlmon]

This code prompts the user which column they want to sum... but JEC's advice to write a sum formula in a cell is the easiest method

Please Login or Register  to view this content.

[TonyATS]

Sorry, that didn't work. It just left a 0 in the B1 cell.
But thanks, anyway.

[TonyATS]

This function is just a part of a larger program that collects weather data from a .cvs file.
It splits the data into sheets, and the Rain sheet data is just the last one.
Thanks, anyway.

[TonyATS]

Thanks, everyone.
None of your suggestions worked quite the way I wanted.
I have another idea I want to try.
CU

[TonyATS]

Boy, I must have had a brain stoppage. (After all, I am 80 years old)
This is my new sub and it works really well.

Sub AddRainColumn()
Range("D2").Select
ActiveCell.FormulaR1C1 = "Rain Amount"
Range("D3").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-991]C:R[-1]C)"
Selection.Copy
Selection.End(xlUp).Select
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Thanks for all your help. Sometimes just viewing help code from Excel people triggers a new thought.
CU


How do I mark this as finished?