How to correctly use FormulaR1C1 to sum dynamic range

Hi all,

Can someone please help with a syntax error I'm getting or offer possible a better solution?

I am trying to sum all the entries in the last column. This a team rota/calendar and the last column and row aren't fixed. For the columns because ecause a months can have 29/30 0r 31 days and for the rows as the team members vary.

I frist thired to use the WorkSheet Function Object, but it then sums immediately and doesn't insert the the actual formula it to the sheet which is necessary as people will add their days's off etc later.

I use through the code LastRow and LastCol to find the last column and last row. The below doesn't work of course, but just to illustrate what I am trying to do.

Cells(LastRow + 1, LastCol + 1).FormulaR1C1 = "=SUM(R5C & LastCol +1:R & LastRow & C & LastCol+1"

I am basically trying to do for xample ...

FormulaR1C1 = "=SUM(R5C31:R25C31)"

but only ever R5 will be fixed the rest won't.

Thanks for any tips. Much appreciated.

Perhaps

Cells(LastRow + 1, LastCol + 1).FormulaR1C1 = "=SUM(R5C" & (LastCol +1) & ":R" & LastRow & "C" & (LastCol+1)

Or perhaps

Cells(LastRow + 1, LastCol + 1).FormulaR1C1 = "=SUM(R5C:R[-1]C)"

I find it easier to test my construction of formula strings with MsgBox or Debug.Print, before coding it to be put in a cell.
And I see the problem, there should be a colon before the last C, as well as a closing parenthisis

Cells(LastRow + 1, LastCol + 1).FormulaR1C1 = "=SUM(R5C" & (LastCol +1) & ":R" & LastRow & ":C" & (LastCol+1) & ")"

Try

myFormula = "=" & d & "-(SUM......."