PDA

View Full Version : Solved: Excel macro for "=sum()"



buhay
11-28-2010, 11:10 AM
Does anyone know a macro procedure that sums up all the numbers above it and shows the sum in the cell below the numbers. It's supposed to replace the exel function "=sum()".

For example sheet1 before the sum function (see sample file):


Colu. A -Colu. B

AAA ------1
AAA-------1
AAA-------1
AAA-------1

BBB ------1
BBB-------1
BBB-------1

FFF ------1
FFF-------1
FFF-------1
FFF-------1

XXX ------1

DDD-------1
DDD-------1


After executing the macro:


Colu. A -Colu. B

AAA ------1
AAA-------1
AAA-------1
AAA-------1
-----------4
BBB ------1
BBB-------1
BBB-------1
-----------3
FFF ------1
FFF-------1
FFF-------1
FFF-------1
-----------4
XXX ------1
-----------1
DDD-------1
DDD-------1
DDD-------1
------------3

Any help is highly appreciated

Bob Phillips
11-28-2010, 11:35 AM
Public Sub ProcessData()
Dim Startrow As Long
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Startrow = 1
For i = 1 To Lastrow + 1

If .Cells(i, "A").Value2 = "" Then

.Cells(i, "B").Formula = "=SUM(B" & Startrow & ":B" & i - 1 & ")"
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

buhay
11-28-2010, 12:25 PM
Thanks for the quick replay. Unfortunately the code you posted doesn't work since it sums all the numbers listed above the cell instead of seperating and considering each categories on columns A.

Using your procedure I end up with the following with my sample file:

AAA ------1
AAA-------1
AAA-------1
AAA-------1
--------- 4
BBB ------1
BBB-------1
BBB-------1
---------11
FFF ------1
FFF-------1
FFF-------1
FFF-------1
--------- 26
XXX ------1
----------53
DDD-------1
DDD-------1
DDD-------1
----------109

Bob Phillips
11-28-2010, 12:58 PM
I forgot to reset Startrow



Public Sub ProcessData()
Dim Startrow As Long
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Startrow = 1
For i = 1 To Lastrow + 1

If .Cells(i, "A").Value2 = "" Then

.Cells(i, "B").Formula = "=SUM(B" & Startrow & ":B" & i - 1 & ")"
Startrow = i +1
End If
Next i
End With

Application.ScreenUpdating = True
End Sub


BTW, are tyhe column A values really repeating or is that just illustrative?

buhay
11-28-2010, 02:14 PM
Awesome mate, thank you so much;)

Sean.DiSanti
11-29-2010, 04:25 PM
another way to skin the cat:
ActiveSheet.UsedRange.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True