VBA sum - doesn't work with only one value.

[Bevy]

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

 

[Bob Phillips]

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]

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]

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]

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

Best Wishes, Bev

 

[Bevy]

Sorry Bob, it works so much better, but one small problem ..... it
doesn't do the bottom sum if there is only one value. does everything
else to perfection, and handles all other single sums fine. It just
appears to be the end one. Any further suggestions??? You look like
you are having a busy day, so hope you can take one more look at this
for me ..... cheers again, bevy

 

[Bevy]

Sorry Bob, it works so much better, but one small problem ..... it
doesn't do the bottom sum if there is only one value. does everything
else to perfection, and handles all other single sums fine. It just
appears to be the end one. Any further suggestions??? You look like
you are having a busy day, so hope you can take one more look at this
for me ..... cheers again, bevy

 

[Bob Phillips]

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]

I am still trying to spot the difference between the two ..... but it
now works for all eventualities. You have been great. Many Many
Thanks.

 

[Bob Phillips]

I changed

Do Until e1 >= lr

to

Do Until e1 > lr


--
HTH

Bob Phillips

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