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

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>, [email protected] 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
>
>

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

"Tushar Mehta" <[email protected]> wrote in
message news:[email protected]:

> 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>, [email protected] 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
> >
> >

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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]:
>
>> 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>, [email protected] 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
>> >
>> >
>

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" <[email protected]> wrote in message
news:#[email protected]:

> 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" <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]:
> >
>
> >> 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>, [email protected] 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
> >> >
> >> >
>
> >

Glad it worked. Thanks,

--
Regards,
Tom Ogilvy

"Jim May" <[email protected]> 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" <[email protected]> wrote in message
> news:#[email protected]:
>
>> 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" <[email protected]> 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" <[email protected]> wrote in
>> > message
>> > news:[email protected]:
>> >
>>
>> >> 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>, [email protected] 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
>> >> >
>> >> >
>>
>> >
>