Subtotal Any Column

[The_Rock]

Hi Folks
I was wondering if there was a way of making a formula dynamic/generic so that it would subtotal any column I want.

The limitations would be that Row 1 always contains the header and that the result (subtotal) should be offset by 2 rows in the same column. So basically, if I have data in (for example) F2:F10, I could click on any cell in Column F and the subtotal would be in F12

This is a formula I use where I am telling it what Columns/Rows to look at.

 Range("X65536").End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUBTOTAL(9,(R2C24:R[-2]C))"

The fields marked with ??? is what I would like to be the active column.

 Range("???65536").End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUBTOTAL(9,(R2C???:R[-2]C))"

 

[The_Rock]

I've found the following on-line, which I have changed to 'Subtotal' instead of sum but I still need to specify which column I want the total in, in the formula. Maybe this could help with some of your suggestions??

Sub Get_Rng_Address()
    Dim rAddress As Range, strAddress As String
    Dim SFormula As String
    Set rAddress = Application.InputBox("Select Range", Type:=8)
    strAddress = rAddress.Address
    Range("B65536").End(xlUp).Offset(2, 0).Value = "=SUBTOTAL(9," & strAddress & ")"
End Sub