Summing columns after insert rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro that goes down row by row and checks the previous rows date.
If it is different it inserts two new blank lines. Once this is completed, I
want to sum the new columns. Using the record macro, it recorded the first
block of numbers and then copied the formula over to the next two columns.

Range("E8").Select
Selection.End(xlDown).Select
Range("E14").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
Range("D14").Select
ActiveCell.FormulaR1C1 = "Total "
Range("E14").Select
Selection.Copy
Range("F14:G14").Select
ActiveSheet.Paste
Range("H14").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[-3]+RC[-2]"
Range("H14").Select
Range("E14").Select
Selection.End(xlDown).Select

to move down to the next block of numbers.
 
Sub AddFormula()

Dim startrow As Long
Dim sumrow As Long
startrow = 8
Do Until Cells(startrow, "E") = ""
sumrow = Cells(startrow, "E").End(xlDown).Row + 1

With Range(Cells(sumrow, "E"), Cells(sumrow, "H"))
.FormulaR1C1 = "=SUM(R" & startrow & "C:R[-1]C)"
End With

startrow = sumrow + 2 ''check!
Loop

End Sub
 
This worked great and it seems to take care of the blocks that just have one
date. I should be able to modify to add formatting. Thanks again.
--
Thanks,
TC


Patrick Molloy said:
Sub AddFormula()

Dim startrow As Long
Dim sumrow As Long
startrow = 8
Do Until Cells(startrow, "E") = ""
sumrow = Cells(startrow, "E").End(xlDown).Row + 1

With Range(Cells(sumrow, "E"), Cells(sumrow, "H"))
.FormulaR1C1 = "=SUM(R" & startrow & "C:R[-1]C)"
End With

startrow = sumrow + 2 ''check!
Loop

End Sub

Tom Cote said:
I have a macro that goes down row by row and checks the previous rows date.
If it is different it inserts two new blank lines. Once this is completed, I
want to sum the new columns. Using the record macro, it recorded the first
block of numbers and then copied the formula over to the next two columns.

Range("E8").Select
Selection.End(xlDown).Select
Range("E14").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
Range("D14").Select
ActiveCell.FormulaR1C1 = "Total "
Range("E14").Select
Selection.Copy
Range("F14:G14").Select
ActiveSheet.Paste
Range("H14").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+RC[-3]+RC[-2]"
Range("H14").Select
Range("E14").Select
Selection.End(xlDown).Select

to move down to the next block of numbers.
Click to expand...
Click to expand...
 
Back
Top