Loop if cell contains "create" then insert row and formula

[tuxo99]

Hello,

i need to make a loop on the first column (i have data from row3 to row 1987654), and if cell contains create, then add two rows above it and two formulas

• =CONCATENATE("formula1";R[4]C[1])
• =CONCATENATE("formula2";R[2]C[1])

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR="class: grid"]
[TD="width: 68"]create a[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]d[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]s[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR="class: grid"]
[TD="width: 64"]df[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR="class: grid"]
[TD="width: 68"]drop[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


¿can you help me?


EDIT:
sorry it wasn´t very well explained.

i need to do the following if in column a it finds create, then insert two rows above and in the first row add the first formula and in then second, the second formula.

thanks

 

[Fluff]

Is this in Excel?
I ask as xl only has 1,048,576 rows

 

[DanteAmor]

If it's for excel, try this:

Sub Macro5()
    Dim u As Double, i As Double
    Application.ScreenUpdating = False
    u = Range("A" & Rows.Count).End(xlUp).Row
    For i = u To 3 Step -1
        If InStr(1, Cells(i, "A"), "Create") > 0 Then
            Rows(i & ":" & i + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Cells(i, "B").FormulaR1C1 = "=CONCATENATE(""formula1"",R[4]C[1])"
            Cells(i + 1, "B").FormulaR1C1 = "=CONCATENATE(""formula2"",R[2]C[1])"
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Another option

Sub tuxo99()
   Dim Ar As Areas
   Dim i As Long
   
   With Range("A:A")
      .Replace "create", "=xxxcreate", xlPart, , False, , False, False
      Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas
      .Replace "=xxxcreate", "create", xlPart, , False, , False, False
   End With
   For i = Ar.Count To 1 Step -1
      Ar(i).Resize(2).EntireRow.Insert xlDown
      Ar(i).Offset(-2).Formula = "=CONCATENATE(""formula1"",R[4]C[1])"
      Ar(i).Offset(-1).Formula = "=CONCATENATE(""formula2"",R[2]C[1])"
   Next i
End Sub