Summing columns after insert rows

[Tom Cote]

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

[Patrick Molloy]

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

[Tom Cote]

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