I have a spreadsheet in the form of:
Cell Data
B1 0.5
B2 0.25
B3 empty
B4 1.0
B5 1.25
B6 0.75
B7 empty

using vb6/vba I'm trying to get totals into the empty cells (i.e. cell
B3 would contain a total for cells B1:B2; cell B7 would contain a
total for cells B4:B6).

Here's what I've got but it isn't working:
ActiveCell.Formula = "=SUM(" & cellRef & ":" & theDiff & ")"

the variable cellRef represents the previous empty cell and the
variable theDiff represents the current empty cell -1.
So in the example above, cellRef might represent cell B3 and theDiff
might represent cell B6.

I'm getting totals but it seems to be a running total rather than a
subtotal. In other words the first subtotal is correct but then the
next subtotal is a total of all the cells above it, not just the cells
since the last subtotal. So in the example above, what I'm currently
getting for the first subtotal is 0.75, which is correct. What I'm
currently getting for the second subtotal is 3.75, which is incorrect;
it should be 3.0.

Try this idea

Sub sumaboveblank()
mc = "f"
lr = Cells(Rows.Count, mc).End(xlUp).Row
'MsgBox lr
For i = lr To 2 Step -1
If Cells(i, mc) = "" Then
br = i
'MsgBox br
nextup = Cells(br - 1, mc).End(xlUp).Row
'MsgBox nextup
Cells(br, mc) = Application.Sum(Range(Cells(nextup, mc), Cells(br, mc)))
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"spowel4" wrote in message
...
I have a spreadsheet in the form of:
Cell Data
B1 0.5
B2 0.25
B3 empty
B4 1.0
B5 1.25
B6 0.75
B7 empty

using vb6/vba I'm trying to get totals into the empty cells (i.e. cell
B3 would contain a total for cells B1:B2; cell B7 would contain a
total for cells B4:B6).

Here's what I've got but it isn't working:
ActiveCell.Formula = "=SUM(" & cellRef & ":" & theDiff & ")"

the variable cellRef represents the previous empty cell and the
variable theDiff represents the current empty cell -1.
So in the example above, cellRef might represent cell B3 and theDiff
might represent cell B6.

I'm getting totals but it seems to be a running total rather than a
subtotal. In other words the first subtotal is correct but then the
next subtotal is a total of all the cells above it, not just the cells
since the last subtotal. So in the example above, what I'm currently
getting for the first subtotal is 0.75, which is correct. What I'm
currently getting for the second subtotal is 3.75, which is incorrect;
it should be 3.0.