About Us

Bernie,

Perfect! Thanks a lot!

"Bernie Deitrick" wrote:

Danny,

Try this version.

Sub SumInActiveCell()
With ActiveCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi Bernie,

I tried the formula and sometimes it works. It also jumps one cell below. It
there a simple way to modify the original formula to make "-4" variable?

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

"Bernie Deitrick" wrote:

Danny,

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
'Uncomment this section to sum from a consistent row: in this example, row 2
'And comment out the lower section of code
'With myCell
' .Formula = "=SUM(" & _
' Range(.Offset(-1, 0), _
' Cells(2, .Column)).Address(False, False) & ")"
'End With

'Use this to sum the block up from the lowest cell
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
.Offset(-1, 0).End(xlUp)).Address(False, False) & ")"
End With
End Sub

HTH,
Bernie
MS Excel MVP

"Danny" wrote in message
...
Hi,

Before I posted this question, I researched on similar questions on this NG
and I didn't get the answer I needed. All I need is a formula that would sum
numbers above the active cell.

This is the formula I have.

ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

It works but it is only good if there are four or less numbers above it.

What I need is a formula for variable numbers to add.

Thank you.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ???	Richard	Excel Discussion (Misc queries)	2	May 13th 23 11:46 AM
How do I find a file/spreadsheet that Excel says is Already open but I can't find it?	nwtrader8	Excel Discussion (Misc queries)	5	June 21st 05 02:16 PM
Stop Excel Rounding Dates	leinad512	Excel Discussion (Misc queries)	1	April 20th 05 04:19 PM
Hints And Tips For New Posters In The Excel Newsgroups	Gary Brown	Excel Worksheet Functions	0	April 15th 05 05:47 PM
Difference in number of Excel NewsGroups	Hari Prasadh	Excel Discussion (Misc queries)	1	January 25th 05 11:32 AM

Menu

About Us