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])

	A	B
1
2
3	create a <tbody> </tbody>
4		d <tbody> </tbody>
5		s <tbody> </tbody>
6		df <tbody> </tbody>
7	drop <tbody> </tbody>

<tbody>
</tbody>

¿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