Copy Formula where cell is blank vba

[Wards199]

Hi,

I have a spreadsheet (example below) where I need the columns F:P summed for the rows above every time column A has 'Total' in it ( so for example with employee 1, columns F:P would be summed in row 26 and would contain the values form F2:P25). As you can see from Employee 1 to Employee 2 the number of rows will differ therefore making it tricky than otherwise would be. I've been playing around with various things in VBA but have yet to find something that does exactly what i require. Any help would be appreciated!

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future this would be helpful as we cannot copy from an image to test with: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this with a copy of your workbook.
I have assumed those numbers are not the result of formulas.

Sub InsertTotals()
  Dim rA As Range
  
  For Each rA In Columns("F:P").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Rows(rA.Rows.Count + 1).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
  Next rA
End Sub

 

[Wards199]

Welcome to the MrExcel board!

For the future this would be helpful as we cannot copy from an image to test with: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this with a copy of your workbook.
I have assumed those numbers are not the result of formulas.

Sub InsertTotals()
  Dim rA As Range
 
  For Each rA In Columns("F:P").SpecialCells(xlConstants, xlNumbers).Areas
    rA.Rows(rA.Rows.Count + 1).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
  Next rA
End Sub

Thanks Peter. Apologies for the image rather than the XL2BB sample.

The code above works fine for the example I sent but it is my fault for not sending a totally accurate example so apologies for this.

With the above code, if there is a blank in the range F:P then the sum gets filled into that blank cell which then would not give an accurate representation in the total column. What I am after, apologies for giving a rubbish example, is the rows where the cell value is Total in column A is for those rows, and only those rows the values above are summed. To help with the explanation, using my example above, if task 2 had 20 blank cells and then 4 cells with values in the sum of all 24 rows would appear in cell G26.

 

[Peter_SSs]

Let's see if column C is a true reflection then.

Sub InsertTotals_v2()
  Dim rA As Range
  
  For Each rA In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).Areas
    rA.Cells(rA.Rows.Count + 1, 4).Resize(, 11).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
  Next rA
End Sub

 

[Wards199]

Let's see if column C is a true reflection then.

Sub InsertTotals_v2()
  Dim rA As Range
 
  For Each rA In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).Areas
    rA.Cells(rA.Rows.Count + 1, 4).Resize(, 11).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
  Next rA
End Sub

Briliant, thanks Peter. That is doing the trick nicely! I'll mark this as the solution shortly but one last thing, not sure I will end up using this but just in case, if I wanted to call this for a specific sheet (let's call this sheet "Main") from another sheet, how would this change the code?

Thanks again

 

[Peter_SSs]

if I wanted to call this for a specific sheet (let's call this sheet "Main") from another sheet, how would this change the code?

Sub InsertTotals_v3()
  Dim rA As Range
  
  With Sheets("Main")
    For Each rA In .Range("C2", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants, xlTextValues).Areas
      rA.Cells(rA.Rows.Count + 1, 4).Resize(, 11).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
    Next rA
  End With
End Sub