For next Loop

[rrocker1405]

Hi,

I'm trying to create loop for the following.

Loop 1:
insert 2 rows for every entry in column A

Loop 2:
Copy the formula in b1 and paste in b4 and then b7 and then b10 until there is an entry in column A

Loop 3:
a. Copy the formula in b2 and paste it in b5 and then in b8 and until there is an entry in column A
b. Copy the formula in b3 and paste it in b3 and then in b6 and until there is an entry in column A

Loop 4:
The loops should repeat until there is a value in each row and in each column. for example for row 1 the above loops should repeat until column k and column A should should repeat until row 12

Attached is the file for your reference.

not sure if all the above loop could be done in a single vba code. please advise.

Thanks in advance.

Kind regards,
A!

 

[rrocker1405]

Hi,

The following code inserts array formulas in the range (c2:AZ63) i specify and then inserts the number of blank rows after every row I need. however, my next step is where i'm stuck at to insert formulas in the blank rows created by the code.

any advise? please. Thanks in advance.

Code:

Sub Loop()
i = 2
For Each c In Range("c2:az63")
c.FormulaArray = "=IFERROR(INDEX(Trace[Child ID],SMALL(IF((RC1=Trace[Parent ID])*(COUNTIF(RC2:RC,Trace[Child ID])=0),ROW(Trace[Parent ID])-MIN(ROW(Trace[Parent ID]))+1,""""),1)),"""")"
i = i + 1
Next
Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")
Set r = Range("A1")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
If r.Offset(1, 0) = "" Then Exit Do
Loop

End Sub

Kind regards,
Anand

 

[Smallman]

Hi

Like to help but I can't see a file with these tables. Can you upload one.

Ta

Smallman