This VBA allows me to look for a specific value within or not in a sentence, it can be a number or a word for instance, and once it finds it, it will add a new row below and I will explain more things, like copying a range then paste it to this new row, put a formula in a specific cell in this new row, etc. I include 2 versions, a simple one without knowing the row number and an “advanced” one including it so I can, for instance, create a formula based on the row ID.

When I use the macro ?

When I have to search a particular value within or not in a sentence then do some specific actions once it finds it.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is/are the macro(s) ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

The simple version without the row ID:

Sub test()
Dim SrchRng As Range
Dim cell As Range
' change D by the column where to search, if you prefer to define your range, put i.e. Range("D2:D13")
Set SrchRng = Range("D2", "D" & Cells(Rows.Count, "D").End(xlUp).Row)
For Each cell In SrchRng
    With cell
        ' change P11 by the value you are searching
        ' to search into a sentence, change "=" by "Like" and put * between the word
        If .Value = "P11" Then
            ' insert new row below
            .Offset(1).EntireRow.Insert
            ' insert XX just below the cell P11, change XX by whatever you want (formula, word, etc.)
            ' to change P11 by XX, change (1) by (0) which same row as P11
            .Offset(1) = "XX"
            ' insert YY to right first cell to cell P11, change YY by whatever you want (formula, word, etc.)
            ' for left first cell, change (1, 1) by (1, -1)
            .Offset(1, 1) = "YY"
            ' copy cell column A to column B of the same row P11 then paste to new row from column A
            Range(.Offset(0, -3), .Offset(0, -2)).Copy .Offset(1, -3)
        End If
    End With
Next cell
End Sub              
              

The advanced version with the row number:

Sub test()
Dim MyVal As String
Dim SrchRng As Long
' change P11 by the value you are searching
' to search into a sentence, change "=" by "Like" and put * between the word
MyVal = "P11"
' change D by the column where to search
SrchRng = Cells(Rows.Count, "D").End(xlUp).Row
' if you prefer to define your range, put i.e. Range("D2:D13") and remove the line above and second line of this code
For Each cell In Range("D2:D" & SrchRng)
    If cell.Value = MyVal Then
    	' insert new row below
        cell.Offset(1).EntireRow.Insert
        ' insert XX just below the cell P11, change XX by whatever you want (formula, word, etc.)
        ' to change P11 by XX, change (1) by (0) which same row as P11
        cell.Offset(1) = "XX"
        ' insert formula using the row ID of P11 which is cell.row = 5
        ' for left first cell, change (1, 1) by (1, -1)
        ' for right cell beside P11, change (1, 1) by (0, 1) and remove + 1
        cell.Offset(1, 1) = "=A" & cell.Row + 1 & "+B" & cell.Row + 1
        ' copy cell column A to column B of the same row P11 then paste to new row from column A
        Range(cell.Offset(0, -3), cell.Offset(0, -2)).Copy cell.Offset(1, -3)
    End If
Next cell
End Sub