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]

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm a little unsure about where your data is and where you want the formulas, but see if one of these heads you in the right direction. Test in a copy of your workbook.

If the numbers are in a single column (I've used B) and are 'constants' (that is, not the results of formulas) then try this

Sub InsertSumFormulasInOneColumn()
  Dim rA As Range
  
  For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(rA.Cells.Count + 1).Formula = "=SUM(" & rA.Address & ")"
  Next rA
End Sub

If each group of numbers is in a separate column (starting at column B (2) and ending wherever the last value in row 1 is), then try this one.

Sub InsertSumFormulasInEachColumn()
  Dim i As Long
  
  For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(Rows.Count, i).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
  Next i
End Sub

 

[Kinsleigh]

Welcome to the MrExcel board!

I'm a little unsure about where your data is and where you want the formulas, but see if one of these heads you in the right direction. Test in a copy of your workbook.

If the numbers are in a single column (I've used B) and are 'constants' (that is, not the results of formulas) then try this

Sub InsertSumFormulasInOneColumn()
  Dim rA As Range
  
  For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Cells(rA.Cells.Count + 1).Formula = "=SUM(" & rA.Address & ")"
  Next rA
End Sub

If each group of numbers is in a separate column (starting at column B (2) and ending wherever the last value in row 1 is), then try this one.

Sub InsertSumFormulasInEachColumn()
  Dim i As Long
  
  For i = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
    Cells(Rows.Count, i).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
  Next i
End Sub

Oh my God. It worked. Your first code worked. Are you able to comment in your code and let me know what is happening. I need to learn VBA asap. This is priceless.

 

[Peter_SSs]

Oh my God. It worked. Your first code worked. Are you able to comment in your code and let me know what is happening. I need to learn VBA asap. This is priceless.

Glad it worked for you.

There isn't much in the code to comment but below is an outline using the sample data shown here.

Excel Workbook
	B
1	Header
2	1
3	6
4	5
5	3
6	4
7
8
9
10	2
11	8
12	6
13
14
15
16	9
17	56
18	23
19	98
20	99
21
Sum Formulas 2

Columns("B").SpecialCells(xlConstants, xlNumbers).Areas

This looks for numbers (constants, not formulas) in column B and records as an "area" each contiguous group. So green, blue and yellow are 3 areas.

For Each rA In Columns("B").SpecialCells(xlConstants, xlNumbers).Areas

For each of those areas in turn..

rA.Cells(rA.Cells.Count + 1).Formula = "=SUM(" & rA.Address & ")"

Go to the cell immediately below the area and put a normal =SUM(xxx) formula where XXX is the address of the area ($B$2:$B$6 for the first one, $B$10:$B$12 for the second etc)

That's about it.

 

[dan_m101]

This coding works nicely, thank you, but I'm interested in a more general case where it only sums up the values above the active cell exactly like Auto Sum does, how would the coding look like then? thanks for any help.

 

[Peter_SSs]

I'm interested .. where it only sums up the values above the active cell ..

Above the active cell up to where? (eg row 2, until an empty cell or the top edge of the sheet is encountered, something else?)

BTW, using the active cell is usually a slower way of doing things. It may be that if you gave a fuller description of what you have, where, and exactly what you are trying to achieve, there may be a more efficient way than using the active cell.

 

[dan_m101]

Hi Peter, what I'm interested is the vba equivalent of Excel's AutoSum feature ie: if I click on a random cell, say E22, then it will sum all values above E22 until the first blank cell and then put SUM(whatever the range is) inside cell E22... thanks again!

 

[Peter_SSs]

Hi Peter, what I'm interested is the vba equivalent of Excel's AutoSum feature ie: if I click on a random cell, say E22, then it will sum all values above E22 until the first blank cell and then put SUM(whatever the range is) inside cell E22... thanks again!

Give this a try. It doesn't exactly match the Autosum behaviour but may suffice for you. If not, specific examples of data, location and expected result would help.

Sub InsertSUMformula()
  Dim fr As Long
  
  With ActiveCell
    Select Case .Row
      Case 1
        fr = 0
      Case 2
        fr = 1
      Case Else
        If IsEmpty(.Offset(-1).Value) Then
          fr = 0
        ElseIf IsEmpty(.Offset(-2).Value) Then
          fr = .Row - 1
        Else
          fr = .Offset(-1).End(xlUp).Row
        End If
    End Select
      If fr > 0 Then
        .FormulaR1C1 = "=SUM(R" & fr & "C:R[-1]C)"
      Else
        .Value = "Error"
      End If
  End With
End Sub

 

[dan_m101]

Works like a charm! But why do you say it might not match Autosum's behavior exactly? (just curious).

 

[Peter_SSs]

Example 1
In column E the only numbers are in E5:E7
You select E22
Autosum will suggest =SUM(E5:E21)
My code gives error because you said to add up to the first empty cell and that is E21 which does not contain a number.

Example 2
Only data on the sheet is numbers in E5:E7 and B22:D22
You select E22
Autosum will suggest =SUM(B22:D22)
but you want to sum vertically, not horizontally.