Well done, you are halfway there. Use the macro recorder, and just do a
normal sum from row 1 to the end of your dataset. Look at the code, and
you will see that the default formula format is FormulaR1C1. But I just
want Formula. So I changed it slightly, and used the Concatenate
operator (&).
In your code, you will have defined LastCol through a different means.
Sub Macro1()
'
' Macro1 Macro
'
Dim LastCol
'
LastCol = 21
'ActiveCell.FormulaR1C1 = "=SUM(R[-21]C:R[-1]C)"
ActiveCell.Formula = "=sum(B1:B" & LastCol & ")"
End Sub