Summing columns after insert rows
 

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.

--
Thanks,
Tom Cote

Summing columns after insert rows
 

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" wrote:

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.

--
Thanks,
Tom Cote

Summing columns after insert rows
 

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" wrote:

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" wrote:

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.

--
Thanks,
Tom Cote