Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to insert rows and columns inside a single cell? | Excel Discussion (Misc queries) | |||
CANT INSERT CELLS, ONLY ROWS & COLUMNS | Excel Worksheet Functions | |||
Insert many columns with many rows AFTER data has already been typ | Excel Discussion (Misc queries) | |||
How do I insert collapsable columns/rows into a worksheet? | Excel Discussion (Misc queries) | |||
summing rows and columns | Excel Discussion (Misc queries) |