Because you want to add another row your need to counters. One to count the
number of times you loop and the other counter to count rows. I like A1
addressing rather than R1C1 addressing. Also I prefer to look forward for
the empty rows by adding 1 to Rowcounter rather than to look backwards. When
you look backwards you have to go through the loop one extra time which is
not necessary. My computer science teacher told me to write efficient code.


Sub sum()

EndRow = Cells(Rows.Count, "O").End(xlUp).Row

FirstRow = 2
RowCount = 2
NumberLoops = EndRow - FirstRow + 1
LoopCount = 1

Do While LoopCount <= NumberLoops

If Range("O" & (RowCount + 1)) = "" Then

Range("O" & (RowCount + 1)).Formula = _
"=SUM(O" & CStr(FirstRow) & ":O" & CStr(RowCount) & ")"
Rows(RowCount + 2).Insert
Range("P" & (RowCount + 2)).Formula = _
"=SUM(P2:P" & CStr(RowCount) & ")+O" & (RowCount + 1)

RowCount = RowCount + 3
FirstRow = RowCount
LoopCount = LoopCount + 2

Else
RowCount = RowCount + 1
LoopCount = LoopCount + 1
End If
Loop

End Sub

"colwyn" wrote:

I have the following code which sums column O until a blank cell is
found. It puts the sum in the blank cell then continues down the
column doing same. I want to have the total go into another column and
row. Can anyone help by tweaking the code so I can achieve this
end ???



Sub sum()

EndRow = Cells(Rows.Count, "O").End(xlUp).Row

firstRow = 2
RowCount = 2
Do While RowCount <= EndRow + 1

If IsEmpty(Cells(RowCount, "O")) Then

lastRow = RowCount - 1
Cells(RowCount, "O").FormulaR1C1 = _
"=SUM(R" & CStr(firstRow) & "C15:R" & _
CStr(lastRow) & "C15)"

firstRow = RowCount + 1
RowCount = RowCount + 1

End If

RowCount = RowCount + 1
Loop

End Sub


Big thanks.
Colwyn.