Home > Enterprise >  Sum with dynamic range
Sum with dynamic range

Time:11-28

I am trying to sum the cells from T to X, and insert the result into cell Y. I'm starting at row 50, and I'd like to end at the last possible row in the sheet so that it works dynamically. Unfortunately, by designating the last row in the table the macro stops working. I've read a lot of tutorials, I've even copied the same macros that people have created and it still doesn't work.

Subb Zad()
    Dim x As Long
    For x = 50 To Cells(Rows.Count, "Y").End(xlUp).Row
    Cells(x, "Y").Formula = Replace("=sum(T@:X@)", "@", x)

    Next
End Sub

CodePudding user response:

No Need for the loop just apply the relative formula to all rows at once:

Sub Zad()
    With ActiveSheet 'good practice to denote the parent sheet even if it is the active sheet
        Dim x As Long
        x = .Cells(.Rows.Count, "T").End(xlUp).Row 'Use T as Y may be empty
        .Range(.Cells(50,"Y"),.Cells(x,"Y")).Formula = "=SUM(T50:X50)"
    End With
End Sub
  • Related