hobbiton73
08-19-2013, 02:12 AM
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
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