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.