Looping Macro to enter negative totals

[Jim May]

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

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

 

[Jim May]

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

 

[Tom Ogilvy]

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

 

[Jim May]

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

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

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

 

[Tom Ogilvy]

Glad it worked. Thanks,

--
Regards,
Tom Ogilvy

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