Macro/VBA help. Insert Sum() to bottom of numbers, continuously in column, till end.

[Kinsleigh]

Hi Everyone,

I need some help to try to overcome this time consuming task in excel. I hope someone here can help.

I have a worksheet that I'm working on, which I have to insert a Sum() Function to the bottom of some numbers in a column to give me the total. (Im working on a single column)When I click the autosum() at the bottom of rows of numbers, Excel intelligently seem to know and auto sum all the numbers above and give me a Total value. Pretend this was 3 rows.

Repeat this process again, but the next time its 4 rows or 5 rows or x number of rows to autosum(). I have like 4000 rows and tonz of sum functions to insert. This is taking me along time to do. I created a macro, which seem promising, but failed, because the macro does not have the intelligent to autosum all the numbers above.

Basically, my macro is using relative Reference, and its. Control down, Control down, down, sum(), enter. end.

Again, the maceo does not have the intelligent to autosum all the numbers above.

Sub Macro11()
'
' Macro11 Macro
'


'
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Please help. Thanks.
Kinsleigh



[TABLE="width: 500"]
<tbody>[TR]
[TD]1
2
3
6 is total




12
2
3
5
22 is total




5
5
5
5
5
25 is total
[TABLE="width: 500"]
<tbody>[TR]
[TD]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dan_m101]

Hello again Peter, sorry for the tardy reply as I had to step out. The negatives you describe are actually exactly how I want the summing to be performed so your coding fits my application perfectly. The only modification I made was to change "Error" to "". Now I wish I could buy you a beer but I see you're from the Canada of the south (Australia) and I'm from the Australia of the north (Canada) =) so it will be rather difficult! But I really want to thank your for saving my posterior, hope I could repay one day!

 

[Peter_SSs]

The only modification I made was to change "Error" to "".

Instead of doing that, consider just removing this bit of code altogether, so you only do anything if fr>0.

    End Select
      If fr > 0 Then
        .FormulaR1C1 = "=SUM(R" & fr & "C:R[-1]C)"
      <del>Else</del>
        <del>.Value = ""</del>
      End If
  End With
End Sub

The negatives you describe are actually exactly how I want the summing to be performed so your coding fits my application perfectly. Now I wish I could buy you a beer but I see you're from the Canada of the south (Australia) and I'm from the Australia of the north (Canada) =) so it will be rather difficult! But I really want to thank your for saving my posterior, hope I could repay one day!

You're welcome. I'll look you up for that beer if I ever get to Canada.

 

[Luis_B]

Hi Rich, how would you make your first code on this thread a negative number please? for example, sum(range)*-1

Thank you,

 

[Peter_SSs]

Hi Rich,

Who is the Rich that you are addressing?