Bob - Thanks very much ... works a treat. I will now try to work out
why!

Best Wishes, Bev


Bob Phillips wrote:

Thank Bevy, much better.

Try this

Sub Bevy()
Dim lr As Long
Dim e1 As Long
Dim e2 As Long
Dim myRange As Range

lr = Cells(Rows.Count, "c").End(xlUp).Row
e1 = Cells(1, "c").End(xlDown).Row

Do Until e1 = lr
If Cells(e1 + 1, "c").Value = "" Then
e2 = e1
Else
e2 = Cells(e1, "c").End(xlDown).Row
End If

Set myRange = Range(Cells(e1, "e"), Cells(e2, "e"))
Cells(e1 - 1, "c") = Application.Sum(myRange)
e1 = Cells(e2, "c").End(xlDown).Row
Loop

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bevy" wrote in message
oups.com...
Thanks for replying Bob,

basically data is collected like this where # = some other data and
column E contains the values to total

A B C D E
# # #
# # # # 10.00
# # # # 20.00
# # # # 30.00
# # #
# # # # 10.00
# # # # 20.00
# # #
# # # # 30.00
# # # # 40.00

I then run the code which does this:-

A B C D E
# # 60.00 #
# # # # 10.00
# # # # 20.00
# # # # 30.00
# # 30.00 #
# # # # 10.00
# # # # 20.00
# # 70.00 #
# # # # 30.00
# # # # 40.00

the amount of rows to total is variable from 1 - many. The loop works
fine but for where there is only one row to total, when it does what I
illustrated earlier.

I had assumed that it worked from the bottom up eg .. 40.00 (last row,
column E) + 30.00 (next to last row, column E) = 70.00 (to be placed in
column C, in the row above the last value in the sum). Looking at it
now though I am not so sure, and not skilled enough to work out exactly
what it is doing!

I really hope this is clear. Any help would be really appreciated,
cheers, Bev




Bob Phillips wrote:
Bev,

The problem will be the e1 - 1 will resolve to 0, and should be easily
resolvable but you need to re-show the data, it is difficult to see what
belongs where. Also, say where the SUM formula should go, I assume in
the
row after the last data, not the first blank cell, but confirm that.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bevy" wrote in message
oups.com...
Hi there, please can anyone help. Have some VBA calculating a sum
from
values in column E. Got the code from this google group, and have
just
about worked out how it works. However, I don't know how to adapt it
so that it does a sum even if there is only one value to sum. I
pressume it is something to do with how the range is calculated.
This
is what I've got.

lr = Cells(Rows.Count, "c").End(xlUp).Row
e1 = Cells(1, "c").End(xlDown).Row

Do Until e1 = lr
e2 = Cells(e1, "c").End(xlDown).Row

Set myrange = Range(Cells(e1, "e"), Cells(e2, "e"))
Cells(e1 - 1, "c") = Application.sum(myrange)

e1 = Cells(e2, "c").End(xlDown).Row
Loop

this is what the data looks like after the macro has been run.

A B C D E

0 b333333 141.88 20/06/2006
1 b333333 125001 1 28.49
1 b333333 125003 1 37.89
1 b333333 125005 1 75.50
0 b222222 108.12 20/06/2006
1 b222222 125007 1 88.34
0 B444444 20/06/2006
1 B444444 125009 1 19.78
1 B444444 039001 1 2.77

I think it should calculate from the bottom value until there is a
blankcell and then offset the total into column C in the row above,
but
it falls over if there is a single value to calculate. It has also
missed the bottom value off this calculation, but all works perfectly
if there is more than one value to sum.

I would be gratefull for any suggestions. Thanks in advance, BevJ