Have VBA/macro repeat process in Excel

[Barry]

I started the vba process below as a macro, but now would like to add to it
code to step down two rows and repeat the process (loop) at that point, maybe
until it reaches a null value (or the first blank row in the spreadsheet.



What I am trying to do is have it insert a new row after every two rows of
data, and sum the two rows of data (in a single column).



Rows("4:4").Select
Selection.Insert Shift:=xlDown
Range("A2:A3").Select
Range("A3").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A2:A4").Select

 

[NickH]

This will do what you want. It is working in column "a" and adding your
formula every third row.

Dim r As Integer

r = 0
Do
r = r + 3
ActiveSheet.Rows(r).Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.Cells(r, "a").FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Loop Until IsEmpty(ActiveSheet.Cells(r + 1, 1))

This assumes that there is an even number of rows in the original data.

 

[Don Guillett]

Try this

Sub insertrowsandsumabove()
r = 4 'assumes header
mc = 1 'column A
lr = Cells(Rows.Count, mc).End(xlUp).Row
Do Until Cells(r - 1, mc) = ""
Rows(r).Insert
Cells(r, mc) = "sum is " & _
Application.Sum(Cells(r - 2, mc).Resize(2))
r = r + 3
Loop
End Sub

 

[Barry]

Thanks Don, it works great!

Nick, thanks as well, but I had a syntax error in the portion of code below:

ActiveSheet.Rows(r).Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove

Try this

Sub insertrowsandsumabove()
r = 4 'assumes header
mc = 1 'column A
lr = Cells(Rows.Count, mc).End(xlUp).Row
Do Until Cells(r - 1, mc) = ""
Rows(r).Insert
Cells(r, mc) = "sum is " & _
Application.Sum(Cells(r - 2, mc).Resize(2))
r = r + 3
Loop
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I started the vba process below as a macro, but now would like to add to it
code to step down two rows and repeat the process (loop) at that point,
maybe
until it reaches a null value (or the first blank row in the spreadsheet.



What I am trying to do is have it insert a new row after every two rows of
data, and sum the two rows of data (in a single column).



Rows("4:4").Select
Selection.Insert Shift:=xlDown
Range("A2:A3").Select
Range("A3").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A2:A4").Select

 

[NickH]

Hi Barry

The line wrapped in the editor - if you put the CopyOrigin text on the same
line as Shift:=xlDown, that will fix it. Alterntaively, put a space followed
by an underscore after the comma on the first line

--
Nick

Thanks Don, it works great!

Nick, thanks as well, but I had a syntax error in the portion of code below:

ActiveSheet.Rows(r).Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove

Try this

Sub insertrowsandsumabove()
r = 4 'assumes header
mc = 1 'column A
lr = Cells(Rows.Count, mc).End(xlUp).Row
Do Until Cells(r - 1, mc) = ""
Rows(r).Insert
Cells(r, mc) = "sum is " & _
Application.Sum(Cells(r - 2, mc).Resize(2))
r = r + 3
Loop
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I started the vba process below as a macro, but now would like to add to it
code to step down two rows and repeat the process (loop) at that point,
maybe
until it reaches a null value (or the first blank row in the spreadsheet.



What I am trying to do is have it insert a new row after every two rows of
data, and sum the two rows of data (in a single column).



Rows("4:4").Select
Selection.Insert Shift:=xlDown
Range("A2:A3").Select
Range("A3").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Range("A2:A4").Select