VBA Insert Formula At The End Of Row

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm trying to put together some code which, starting at row 5 checks to see if there is a value in column B. If there is sum the values in the columns C:M for that row and divide by 7.4.

From some tutorials I've ben reading I've put together the following.


Sub InsertTotals()
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim ws As Worksheet
StartRow = 5
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads", "Projects"))
Set EndCell = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, 1)
If EndCell.Row > StartRow Then
EndCell.Offset(, 13).Formula = "=Sum(R" & StartRow & "C:R[-1]C)/7.4"
End If
ws.Columns("B:O").EntireColumn.AutoFit
Next ws
End Sub

Although the code runs, it's not producing the desired result, and I know it must have something to do with this line:
EndCell.Offset(, 13).Formula = "=Sum(R" & StartRow & "C:R[-1]C)/7.4"
but I'm a little unsure about what I need to change.

I just wondered whether someone may be able to look at this please and let me know where I've gone wrong.

Could I also trouble you to include comments because I'm keen to learn how to put this and other formulas together.

Many thanks and kind regards

[lynnnow]

Do you want to use only the "R1C1" formula style? Why not use the xlA1 style?

[hobbiton73]

Hi @lynnnow, thank you for taking the time to reply to my post.

From the limited experience I have, I think that the R1C1 may be easier to understand, but I'm more than happy to proven wrong.

Many thanks and kind regards

[snb]

Why don't you put the formula =SUM(C5:M5) in column L manually ?

[lynnnow]

Or if you want to show the formula only if it calculates based on a value in column B, the use the ISBLANK() function.

[hobbiton73]

Hi @snb, thank you for taking to look at, and reply to my post.

To try and lessen manual intervention, I was trying to do this via VBA.

Many thanks and kind regards