add sum with macro to list

M

mohavv

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
 
M

Mike

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
 
D

Dave Peterson

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)"
 
Top