Dynamic forumulas using VBA

[brs_prs]

Hi,


I need to calculate sum and other math calculations in a sheet. The rows are not always the same.I recorded a macro to see how the code looks like but it only takes the current cell. How to make it dynamic.

The code I tried is below:

Sub Sum_per_format()
    Columns("Y:Y").Select
    Selection.Insert Shift:=xlToRight
    Range("Y1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Sum"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[38166]C[-1])"
    Range("X3").Select
    Range("Z1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Percent"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = _
        "=1-(SUM(RC[-3]:R[38166]C[-3])/SUM(RC[-4]:R[38166]C[-4]))"
    Range("Z3").Select
    Range("Z2").Select
    Selection.NumberFormat = "0%"
    Rows("1:1").Select
    Range("M1").Activate
    Selection.Font.Bold = True
    Range("Y2:Z2").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Cells.Select
    Range("M1").Activate
    With Selection.Font
        .Name = "Arial"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
End Sub

Am calculating the sum of a column and posting the result in another cell.
Rows are dynamic. How to achieve it?
Any small help is greatly apapreciated.