VBA formula to sum with variables

[mrpman97]

I want to have formula with: ActiveCell:formula =
I want to sum columns in a row having a column variable called: col
The col variable will be the far right column and the other colunm in Row 2 will be col-3.
What is the syntax to create this formula?
If col = 5, formula normally would be :sum(b2:c5), I want to use col as the varialbe.

 

[shg]

Maybe ...

    Dim iCol As Long
    
    iCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
    ActiveCell.FormulaR1C1 = "=sum(r2c" & iCol & ":rc2)"

 

[mrpman97]

Thans, it works beautifully

 

[mrpman97]

Formula worked but I want to copy this formula down. Need formula to be without $.
Thanks