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

tuxo99

Board Regular
Joined
Aug 13, 2015
Messages
75
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])


AB
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
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,399
Office Version
365
Platform
Windows
Is this in Excel?
I ask as xl only has 1,048,576 rows
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,932
Office Version
2007
Platform
Windows
If it's for excel, try this:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,399
Office Version
365
Platform
Windows
Another option
Code:
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
 

Forum statistics

Threads
1,082,505
Messages
5,365,965
Members
400,864
Latest member
RobynP51

Some videos you may like

This Week's Hot Topics

Top