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