Solved: Excel macro for "=sum()"

[buhay]

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]

[vba]

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
[/vba]

[buhay]

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]

I forgot to reset Startrow

[vba]

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
[/vba]

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

[buhay]

Awesome mate, thank you so much

[Sean.DiSanti]

another way to skin the cat:
[VBA] ActiveSheet.UsedRange.Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True[/VBA]