Simple macro help...

Posted by Cory on July 26, 2001 12:47 PM

I have a column containing groups of numbers. In between these groups is a blank cell. I need a macro that will go to each blank cell and sum the group above it, move onto the next blank cell and do it again, so on and so forth. Some of these groups only have one value in it (which is where I'm getting stuck). Here's an example of what I mean:

10
10
20
35
(blank)
12
13
(blank)
2
6
8
10
12
19
25
(blank)
21
(blank)
32
18
45
.......

In each blank a formula would be inserted adding the number directly above it, even if it's only one number (like the lone 21 would be the sum of itself; 21)

Thanks for the help...

Cory

Posted by Barrie Davidson on July 26, 2001 1:32 PM

Hi Cory, I think this code will work for you (note that it assumes your data is in column A).

Dim RowNumber As Integer
Dim LastRow As Integer

'You need to declare the last row of your data before this part
Range("A1").Select
Do Until ActiveCell.Row > LastRow
RowNumber = ActiveCell.Row
If ActiveCell.Offset(1, 0).Value = Empty Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=" & ActiveCell.Offset(-1, 0).Address
Else
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveCell.Formula = "=Sum(A" & RowNumber & ":" & ActiveCell.Offset(-1, 0).Address & ")"
End If
ActiveCell.Offset(1, 0).Select
Loop

Posted by Cory on July 26, 2001 2:20 PM

Thanks, Barry!

Thanks!! Worked great!!