add sum with macro to list
 

Hi,

How can I add a SUM function via a macro underneath a list which can
differ in length.
I know how to get to the bottom of the list using CTRL-DOWN in
"relative mode" but then I get into trouble with the range in the
formula.

Any help on this is welcome.

Cheers,

Harold

add sum with macro to list
 

Try this will sum A2 to last row
With Worksheets("Sheet1")
If IsEmpty(.Cells(.Rows.Count, 1)) Then
With .Cells(.Rows.Count, 1).End(xlUp)
.Offset(2, 0).Formula = "=Sum($A$2:" & _
.Address & ")"
End With
End If
End With

"mohavv" wrote:

Hi,

How can I add a SUM function via a macro underneath a list which can
differ in length.
I know how to get to the bottom of the list using CTRL-DOWN in
"relative mode" but then I get into trouble with the range in the
formula.

Any help on this is welcome.

Cheers,

Harold

add sum with macro to list
 

This uses column A to find the next row:

Option Explicit
Sub testme()
Dim NextRow As Long
With ActiveSheet
'use column A to determine the NextRow
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(NextRow, "A").FormulaR1C1 = "=sum(r1c:r[-1]c)"
End With
End Sub

If you wanted to fill in (say) columns B:F with the similar formula (avoiding
column A):

Option Explicit
Sub testme()
Dim NextRow As Long
With ActiveSheet
'use column A to determine the NextRow
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(NextRow, "B").Resize(1, 5).FormulaR1C1 = "=sum(r1c:r[-1]c)"
End With
End Sub

A note about the .FormulaR1C1 reference style:
=sum(r1c:r[-1]c)
r1c is the first row in the same column as the cell with the formula (Row 1,
same column)

r[-1]c is one cell up from the cell with the formula in the same column.

If you wanted Row 2 through one cell up, you'd use:
..FormulaR1C1 = "=sum(r2c:r[-1]c)"



mohavv wrote:

Hi,

How can I add a SUM function via a macro underneath a list which can
differ in length.
I know how to get to the bottom of the list using CTRL-DOWN in
"relative mode" but then I get into trouble with the range in the
formula.

Any help on this is welcome.

Cheers,

Harold

--

Dave Peterson