Using Lastcolumn in formula

[coop123]

Good Morning

As part of a macro I am using I want to add a formula in M12 to sum all the values in every 3rd cell along row 12 from starting point O12 to last column. the last column will vary.

The formula that works is but this always has same range.

ActiveCell.Formula2R1C1 = _
"=SUMPRODUCT((MOD(COLUMN(RC[2]:RC[37])-COLUMN(RC[2]),3)=0)*1,RC[2]:RC[37])"

I have DIM LastColumn but don't know how to fit it into the above formula.

Dim LastColumn As Long
LastColumn = Cells(10, Columns.Count).End(xlToLeft).Column

I also need to copy the formula down to last row in column M.

Any help would be very much appreciated.

Thanks

Coop123

 

[Peter_SSs]

I also need to copy the formula down to last row in column M.

Since M is the column the formula is going in, we most likely need a different column to determine the end of the data. I have assumed column O for that.

Sub Insert_Formula()
  Dim LastColumn As Long
 
  LastColumn = Cells(10, Columns.Count).End(xlToLeft).Column
  Range("M12:M" & Range("O" & Rows.Count).End(xlUp).Row).Formula2R1C1 = _
    Replace("=SUMPRODUCT((MOD(COLUMN(RC[2]:RC#)-COLUMN(RC[2]),3)=0)*1,RC[2]:RC#)", "#", LastColumn)
End Sub

 

[coop123]

Hi Peter

Your assumption was correct, it works perfectly.

I have also learnt a new way of copying the formula down without having to have more lines of code to move columns and fill down.

Thank you again.

coop123

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.