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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA sum - doesn't work with only one value. (https://www.excelbanter.com/excel-programming/364937-vba-sum-doesnt-work-only-one-value.html)

Bevy

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

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

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

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

Bevy

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

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

Bob Phillips

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

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

Bevy

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

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

Bevy

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

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

Bevy

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

Bob Phillips

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

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...
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 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

All times are GMT +1. The time now is 06:23 AM.