Add sum formula with an offset from selected cell - vba

[TitoElan]

I have a code for adding group of rows from another Worksheet. The number of rows is always different. Under that group I will add some more.
When I run the code I get something like this. When I run the code the active.cell is always the one where I want to add the sum formula.

The number of rows in the middle are always different. The number of them is set with a variable "Dim nbr As Long".
Somehow (with an offset?) I want to add a sum formula in the active cell. In that case the sum formula should sum the range P2:P7.

I would be very grateful for some help

 

[Alex Blakenburg]

Do you mean something like this ?

Sub InsertSum()

    Dim ws As Worksheet
    Dim rowActive As Long, colActive As Long
    Dim rowTop
    
    Set ws = ActiveSheet
    
    rowActive = ActiveCell.Row
    colActive = ActiveCell.Column   ' <-- Use this if you want to use it in any column
    rowTop = ws.Cells(rowActive - 1, colActive - 1).End(xlUp).Row
    
    With ws
        ActiveCell.Formula = "=Sum(" & .Cells(rowTop, colActive - 1).Address & ":" & _
                                        .Cells(rowActive - 1, colActive - 1).Address & ")"
    End With

End Sub

 

[TitoElan]

Do you mean something like this ?

Sub InsertSum()

    Dim ws As Worksheet
    Dim rowActive As Long, colActive As Long
    Dim rowTop
   
    Set ws = ActiveSheet
   
    rowActive = ActiveCell.Row
    colActive = ActiveCell.Column   ' <-- Use this if you want to use it in any column
    rowTop = ws.Cells(rowActive - 1, colActive - 1).End(xlUp).Row
   
    With ws
        ActiveCell.Formula = "=Sum(" & .Cells(rowTop, colActive - 1).Address & ":" & _
                                        .Cells(rowActive - 1, colActive - 1).Address & ")"
    End With

End Sub

yes! Thanks!

 

[Alex Blakenburg]

You're welcome.
One suggestion, if you know the cell on the total line of the amounts you are summing is always going to be empty, if you change the Sum to include that cell then if you insert a row before the total it will automatically get included in the sum.
Just change rowActive - 1 to rowActive