Excel macro to insert function

Question

I'm trying to write a macro that will place a formula under a column of numbers, such as sum or average. I came up with this:

Sub foo()
    ActiveSheet.Range(ActiveCell, ActiveCell.End(xlDown)).Select
    ActiveCell.End(xlDown).Offset(1) = Application.WorksheetFunction.Sum(Selection)
End Sub

This function will find the sum and place it under the last number in a column of data that you have selected the top of. That works but it is not dynamic, the new value is just a value, if I change one of the numbers in the column, the cell holding the sum does not change. So I'm trying to place the actual function in the cell. This is what I have currently:

Sub foo()
    ActiveCell.Offset(1).Formula = "=Sum(ActiveCell:ActiveCell.End(xlUp))"
End Sub

This time I started at the bottom, the active cell is currently intended to be the last value in the column. No errors are detected but the output is bad, "#NAME". I tried playing with the quotes but didn't get anything. For example:

Sub foo()
    ActiveCell.Offset(1).Formula = "=Sum(" & ActiveCell:ActiveCell.End(xlUp) & ")"
End Sub

Any ideas?

Answer 1

Consider:

Sub dural()
    Dim s As String
    s = Range(ActiveCell, ActiveCell.End(xlDown)).Address
    ActiveCell.End(xlDown).Offset(1, 0).Formula = "=SUM(" & s & ")"
End Sub

Answer 2

If your data always starts at row 1 then you can use the macro as such:

ActiveCell.Offset(1).ForumulaR1C1 = "=SUM(INDIRECT(ADDRESS(1,COLUMN()) & "":"" & ADDRESS(ROW()-1,COLUMN())))"

Answer 3

Gary's Student's answer is very close but not quite there, this is what I ended up with:

Sub foo()
    Dim s As String
    s = Range(ActiveCell, ActiveCell.End(xlUp)).Address(0,0)
    ActiveCell.Offset(1).Formula = "=SUM(" & s & ")"
End Sub

If you select the last cell in a column of data it will place the sum formula right under it, and its dynamic so you're free to change the data.

EDIT: added (0,0) onto address so its dynamic.