Looping Macro to enter negative totals
 

Below is a small sample of amts in Col E starting in row 5 which I'de
Like to have a macro enter (the totals) in
E6 -702.96
E9 -1,093.69
E11 -167.92
E15 -502.11
But for the life of me I can't get it going.
Can someone assist?
TIA,


Row E
5 702.96
6
7 285.85
8 807.84
9
10 167.92
11
12 60.28
13 145.1
14 296.73
15

Looping Macro to enter negative totals
 

You don't need VBA to do what you want, especially if this is a one-time or
a rather infrequent event. But, if you must, use the macro recorder (Tools
| Macro Record new macro...) to record the below.

Select column E, then Edit | Go to... | Special... button. In the next
dialog box, select the 'Blanks' option and click OK. This will select
everything but E15. So, hold down CTRL and select E15. Now, enter the
formula=-SUM($E$5:E14) and complete the task with CTRL+ENTER. Note the use
of both absolute and relative addresses in the formula.

If you did record the above, turn off the recorder and switch to the VBE.
In most cases, XL will have generated the relevant code. In addition, if
you share it here, someone should be able to generalize it for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <jlSwg.103656$IZ2.89672@dukeread07, says...
Below is a small sample of amts in Col E starting in row 5 which I'de
Like to have a macro enter (the totals) in
E6 -702.96
E9 -1,093.69
E11 -167.92
E15 -502.11
But for the life of me I can't get it going.
Can someone assist?
TIA,


Row E
5 702.96
6
7 285.85
8 807.84
9
10 167.92
11
12 60.28
13 145.1
14 296.73
15

Looping Macro to enter negative totals
 

Tushar, thanks for the reply;
I tried your suggestion, but get a Circular Reference problem;
I have 500 + rows..

"Tushar Mehta" wrote in
message om:

You don't need VBA to do what you want, especially if this is a one-time or
a rather infrequent event. But, if you must, use the macro recorder (Tools
| Macro Record new macro...) to record the below.

Select column E, then Edit | Go to... | Special... button. In the next
dialog box, select the 'Blanks' option and click OK. This will select
everything but E15. So, hold down CTRL and select E15. Now, enter the
formula=-SUM($E$5:E14) and complete the task with CTRL+ENTER. Note the use
of both absolute and relative addresses in the formula.

If you did record the above, turn off the recorder and switch to the VBE.
In most cases, XL will have generated the relevant code. In addition, if
you share it here, someone should be able to generalize it for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <jlSwg.103656$IZ2.89672@dukeread07, says...

Below is a small sample of amts in Col E starting in row 5 which I'de
Like to have a macro enter (the totals) in
E6 -702.96
E9 -1,093.69
E11 -167.92
E15 -502.11
But for the life of me I can't get it going.
Can someone assist?
TIA,


Row E
5 702.96
6
7 285.85
8 807.84
9
10 167.92
11
12 60.28
13 145.1
14 296.73
15

Looping Macro to enter negative totals
 

Sub MakeSums()
Dim lastrow As Long, i As Long, rng As Range
lastrow = Cells(Rows.Count, "E").End(xlUp).Row + 1
For i = lastrow To 5 Step -1
If Cells(i, "E") = "" Then
If i = 6 Or Cells(i - 2, "E") = "" Then
Cells(i, "E").Value = "=E" & i - 1
Else
Set rng = Range(Cells(i - 1, "E"), Cells(i - 1, "E").End(xlUp))
Cells(i, "E").Formula = "=Sum(" & rng.Address(0, 0) & ")"
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy



"Jim May" wrote in message
news:2bTwg.103666$IZ2.59354@dukeread07...
Tushar, thanks for the reply;
I tried your suggestion, but get a Circular Reference problem;
I have 500 + rows..

"Tushar Mehta" wrote in message
om:

You don't need VBA to do what you want, especially if this is a one-time
or
a rather infrequent event. But, if you must, use the macro recorder
(Tools
| Macro Record new macro...) to record the below.

Select column E, then Edit | Go to... | Special... button. In the next
dialog box, select the 'Blanks' option and click OK. This will select
everything but E15. So, hold down CTRL and select E15. Now, enter the
formula=-SUM($E$5:E14) and complete the task with CTRL+ENTER. Note the
use
of both absolute and relative addresses in the formula.

If you did record the above, turn off the recorder and switch to the VBE.
In most cases, XL will have generated the relevant code. In addition, if
you share it here, someone should be able to generalize it for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <jlSwg.103656$IZ2.89672@dukeread07, says...

Below is a small sample of amts in Col E starting in row 5 which I'de
Like to have a macro enter (the totals) in
E6 -702.96
E9 -1,093.69
E11 -167.92
E15 -502.11
But for the life of me I can't get it going.
Can someone assist?
TIA,


Row E
5 702.96
6
7 285.85
8 807.84
9
10 167.92
11
12 60.28
13 145.1
14 296.73
15

Looping Macro to enter negative totals
 

Tom, I am going to name my "NEXT-BORN" -- "Tom Ogilvy May"!!!
But turning 62 this year - I'm a bit reluctant. You are
much appreciated by this Group.
Thanks for your high-level expertise.
You can always take your proposed solution "to-the-bank".

Jim

"Tom Ogilvy" wrote in message
:

Sub MakeSums()
Dim lastrow As Long, i As Long, rng As Range
lastrow = Cells(Rows.Count, "E").End(xlUp).Row + 1
For i = lastrow To 5 Step -1
If Cells(i, "E") = "" Then
If i = 6 Or Cells(i - 2, "E") = "" Then
Cells(i, "E").Value = "=E" & i - 1
Else
Set rng = Range(Cells(i - 1, "E"), Cells(i - 1, "E").End(xlUp))
Cells(i, "E").Formula = "=Sum(" & rng.Address(0, 0) & ")"
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy



"Jim May" wrote in message
news:2bTwg.103666$IZ2.59354@dukeread07...

Tushar, thanks for the reply;
I tried your suggestion, but get a Circular Reference problem;
I have 500 + rows..

"Tushar Mehta" wrote in message
om:


You don't need VBA to do what you want, especially if this is a one-time
or
a rather infrequent event. But, if you must, use the macro recorder
(Tools
| Macro Record new macro...) to record the below.

Select column E, then Edit | Go to... | Special... button. In the next
dialog box, select the 'Blanks' option and click OK. This will select
everything but E15. So, hold down CTRL and select E15. Now, enter the
formula=-SUM($E$5:E14) and complete the task with CTRL+ENTER. Note the
use
of both absolute and relative addresses in the formula.

If you did record the above, turn off the recorder and switch to the VBE.
In most cases, XL will have generated the relevant code. In addition, if
you share it here, someone should be able to generalize it for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <jlSwg.103656$IZ2.89672@dukeread07, says...


Below is a small sample of amts in Col E starting in row 5 which I'de
Like to have a macro enter (the totals) in
E6 -702.96
E9 -1,093.69
E11 -167.92
E15 -502.11
But for the life of me I can't get it going.
Can someone assist?
TIA,


Row E
5 702.96
6
7 285.85
8 807.84
9
10 167.92
11
12 60.28
13 145.1
14 296.73
15

Looping Macro to enter negative totals
 

Glad it worked. Thanks,

--
Regards,
Tom Ogilvy

"Jim May" wrote in message
news:PoUwg.103669$IZ2.5136@dukeread07...
Tom, I am going to name my "NEXT-BORN" -- "Tom Ogilvy May"!!!
But turning 62 this year - I'm a bit reluctant. You are
much appreciated by this Group.
Thanks for your high-level expertise.
You can always take your proposed solution "to-the-bank".

Jim

"Tom Ogilvy" wrote in message
:

Sub MakeSums()
Dim lastrow As Long, i As Long, rng As Range
lastrow = Cells(Rows.Count, "E").End(xlUp).Row + 1
For i = lastrow To 5 Step -1
If Cells(i, "E") = "" Then
If i = 6 Or Cells(i - 2, "E") = "" Then
Cells(i, "E").Value = "=E" & i - 1
Else
Set rng = Range(Cells(i - 1, "E"), Cells(i - 1, "E").End(xlUp))
Cells(i, "E").Formula = "=Sum(" & rng.Address(0, 0) & ")"
End If
End If
Next
End Sub

--
Regards,
Tom Ogilvy



"Jim May" wrote in message
news:2bTwg.103666$IZ2.59354@dukeread07...

Tushar, thanks for the reply;
I tried your suggestion, but get a Circular Reference problem;
I have 500 + rows..

"Tushar Mehta" wrote in
message
om:


You don't need VBA to do what you want, especially if this is a
one-time
or
a rather infrequent event. But, if you must, use the macro recorder
(Tools
| Macro Record new macro...) to record the below.

Select column E, then Edit | Go to... | Special... button. In the
next
dialog box, select the 'Blanks' option and click OK. This will select
everything but E15. So, hold down CTRL and select E15. Now, enter
the
formula=-SUM($E$5:E14) and complete the task with CTRL+ENTER. Note
the
use
of both absolute and relative addresses in the formula.

If you did record the above, turn off the recorder and switch to the
VBE.
In most cases, XL will have generated the relevant code. In addition,
if
you share it here, someone should be able to generalize it for you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <jlSwg.103656$IZ2.89672@dukeread07, says...


Below is a small sample of amts in Col E starting in row 5 which
I'de
Like to have a macro enter (the totals) in
E6 -702.96
E9 -1,093.69
E11 -167.92
E15 -502.11
But for the life of me I can't get it going.
Can someone assist?
TIA,


Row E
5 702.96
6
7 285.85
8 807.84
9
10 167.92
11
12 60.28
13 145.1
14 296.73
15