need SUM code help

[colwyn]

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.

 

[Joel]

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" & 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

 

[Bob Bridges]

If I understand you, Colwyn, you want to run this code on more than one
column, not just O but some other column(s) too. If the columns are
contiguous you can set up a loop from column 11 to 13, say, but if not
probably the simplest thing to do is define a column-number parameter to the
Sub; the calling routine can call it with argument 12 for column O, then 6
for column F, and so on as long as you like.

I've tweaked your code in two ways. First, I changed your loop to a
For....Next loop, which is perfectly designed for running from row m to row
n. I named the loop index thisRow to go with firstRow and endRow, but of
course that's up to you.

Second, I added the parm ColNbr to the sub, and wherever 15 appears as a
literal in the sub I used ColNbr instead. You can no doubt find and fix any
bugs I accidentally introduced.

Oh, and I forgot: Third, I changed the Sub name from "sum" to "AddSum".
Come on, you don't want to name a Sub the same as an alread-existing
function; think of all the confusion it might cause.

Sub AddSum(ColNbr)
EndRow = Cells(Rows.Count, "O").End(xlUp).Row
firstRow = 2

For thisRow = 2 to EndRow
If IsEmpty(Cells(thisRow, "O")) Then
Cells(thisRow, "O").FormulaR1C1 = "=SUM(R" & _
CStr(firstRow) & "C" & ColNbr & ":R" & _
CStr(thisRow - 1) & "C" & ColNbr & ")"
thisRow = thisRow + 1
firstRow = thisRow
End If
Next thisRow

End Sub