VBA Insert Formula On Multiple Sheets

[hobbiton73]

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

I'm trying to put a script together which will perform the following.

• Look at the sheets "Enhancements", "Indirect" and "Overheads".
• Find the first blank row where cells in columns B:N are empty.
• Insert a sum formula in each of these columns which sums the rows above starting at row 4.

I've made a start below but unfortunately I recieve a error when I try to run this.

Sub InsertTotals()
Dim StartRow As Integer
Dim EndRow As Integer
Dim ws As WorksheetStartRow = 4
For Each ws In Worksheets(Array("Enhancements", "Indirect", "Overheads")).Range("C3")
EndRow = Range("C65536").End(xlUp).Offset(1, 0).Row
For i = StartRow To EndRow
If Cells(i, "C") = "" And i > StartRow Then
Cells(i, "C").Formula = "=SUM(C" & StartRow & ":C" & i - 1 & ")"
StartRow = i + 1
End If
Next
Next ws
End Sub

I'm relatively new to VBA, so perhaps the error is quite simple to the more seasoned developer.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about acheiving this.

Many thanks and kind regards

[p45cal]

Will all the SUM formulae end up on the same row on a given sheet (or do you expect them perhaps to be staggered)?

[hobbiton73]

Hi @p45cal, thnak you very much for taking the time to reply to my post. I can confirm that the formulae will be staggered, because each sheet may have a different number of entries. Many thanks and kind regards.

[p45cal]

try:

Sub InsertTotals()
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim ws As Worksheet
StartRow = 4
For Each ws In Worksheets(Array("Enhancements", "Indirect", "Overheads"))
  For colm = 2 To 14  '(B to N)
    Set EndCell = ws.Cells(Rows.Count, colm).End(xlUp).Offset(1)
    EndCell.Formula = "=SUM(" & Cells(StartRow, colm).Address & ":" & EndCell.Offset(-1).Address & ")"
  Next colm
Next ws
End Sub

[hobbiton73]

Hi @p45cal, thank you for taking the time to reply to my post and put the solution together.

I've tried the code, but unfortunately it's not quite having the desired effect.

If my data is as follows:

Column B

Column C

Column D

Column E

Column F

Column G

Column H

Column I

Column J

Column K

Column L

Column M

Column N

B&C General Enhancements C&R FY13

here

here

here

here

here

here

here

here

here

95.9

113.35

here

Enhancements

428

265

21.05

1522

The formula is being paste into the cells I've added the word 'here'.

Could you perhaps tell me please do you think it would be possible to use column B as the key, i.e. starting at row 4 find the next empty cell. When it finds this then insert the formula.

In addition, and I appreciate that this a change to my original requirements, but do you think it would be possible please for the formula to be only added if there is at least one row of information, i.e if cell B4 is empty do not add the formula to the sheet.

Thank you once again for your help.

Many thanks and kind regards

[p45cal]

Could you perhaps tell me please do you think it would be possible to use column B as the key, i.e. starting at row 4 find the next empty cell. When it finds this then insert the formula.

I'm not clear..

Will all the SUM formulae end up on the same row on a given sheet (or do you expect them perhaps to be staggered)?

are you now saying that the formulae are NOT staggered?
While there's good column info in your table, I can't tell what the row numbers are. Could you attach a workbook with you next post?

[hobbiton73]

@p45cal, thank you for coming back to me with this.

Unfortunately I'm unable to attach a file at the moment because I'm not at home and won't return until this evening, but if it helps, I've tried to illustrate this a little better below by providing an example of how the sheets may look.

Overheads Sheet

Column B

Column C

Column D

Column E

Column F

Column G

Column H

Column I

Column J

Column K

Column L

Column M

Column N

Row 3

Column Headings

Row 4

B&C General Enhancements C&R FY13

3

15

20

Row 5

Enhancements

25

5

2

Row 6

13-14 Enhancements

2

18

Enhancements sheet

Column B

Column C

Column D

Column E

Column F

Column G

Column H

Column I

Column J

Column K

Column L

Column M

Column N

Row 3

Column Headings

Row 4

B&C General Enhancements C&R FY13

3

15

20

Row 5

Enhancements

25

5

2

Indirect sheet

Column B

Column C

Column D

Column E

Column F

Column G

Column H

Column I

Column J

Column K

Column L

Column M

Column N

Row 3

Column Headings

Hopefully this will illustrate that the sheets have a different number of rows populated. You can see that the first two sheets have rows populated with data whereas the last (Indirect) sheet has no data.

So in terms of the formula, this would need to be on row 7 for the "Overheads" sheet, row 6 for the "Enhancements" sheet and there would be no need to insert the formula on the "Indirect" sheet because it contains no data.

I hope this helps.

Many thanks and kind regards

[p45cal]

try:

Sub InsertTotals()
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim ws As Worksheet
StartRow = 4
For Each ws In Worksheets(Array("Enhancements", "Indirect", "Overheads"))
  Set EndCell = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, 1)
  If EndCell.Row > StartRow Then EndCell.Resize(, 12).Formula = "=SUM(R" & StartRow & "C:R[-1]C)"
Next ws
End Sub

[hobbiton73]

Hi @p45cal, thank you for coming back to me with this it works great.

Many thanks for all your help, again, and kind regards