Macro for seaching a phrase and add 7 rows after that phrase

Select range (or column) and run this macro:

It will prompt you for phrase so you can type whatever and then run.

Public Sub SearchPhrase()
Dim rngArea As Range
Dim myPhrase As String

myPhrase = Application.InputBox(prompt:="Search for phrase:", Type:=2)
If Len(myPhrase) > 0 Then
For Each rngArea In Selection
    With rngArea
        If InStr(UCase(rngArea), UCase(myPhrase)) > 0 Then
            rngArea.Offset(1).Resize(7).Insert Shift:=xlDown
            Exit Sub 'remove this if you want to have multiple search
        End If
    End With
Next rngArea
End If
End Sub

It will end after first match.
If you want to add 7 rows after each match remove "Exit Sub" part

Try this:

Option Explicit

Sub Love()
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row
Dim i As Long
Dim res As String
res = "I Love You"

For i = lr To 1 Step -1
If Range("B" & i) = res Then
Range("B" & i + 1 & ":B" & i + 7).EntireRow.Insert
End If
Next i
End Sub

Select range.. Like put pointer on some cell, click left button on the mouse and drag down...
So cells become blue. You can select also whole (B) column by pressing B at the top of the sheet

pattern-dragged-down.png

Then run macro.

VBA does not play well with merged cells. Can you change your layout to avoid using merged cells? Otherwise, you may have to add vba steps to unmerge your cells, run your code and then re-merge your cells. This may or may not give you the expected results. Personally, I avoid merging cells especially if I am working in a VBA environment.

Use this for entire row:

Public Sub SearchPhrase()
Dim rngArea As Range
Dim myPhrase As String

myPhrase = Application.InputBox(prompt:="Search for phrase:", Type:=2)
If Len(myPhrase) > 0 Then
For Each rngArea In Selection
    With rngArea
        If InStr(UCase(rngArea), UCase(myPhrase)) > 0 Then
            rngArea.Offset(1).Resize(7).EntireRow.Insert Shift:=xlDown
            Exit Sub 'remove this if you want to have multiple search
        End If
    End With
Next rngArea
End If
End Sub

Did you try the code I supplied in post 4 as it worked for me when I tested.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks for the rep.