How can I add a row with a certain keyword after any other row that has that phrase?

[sr1111]

How can I add a row with a certain keyword after any other row that has that phrase?
For instance, if the search term "after the break" is found in the row above, I want to add a row with the keyword "Keyword." below it.

 

[sr1111]

I tried this but this does not work in the loop

Sub water()
With Cells.Find("after the break:")
    .EntireRow.Insert
    With Cells(.Row + 1, "A")
        .Value = "Keyword."
        .Font.Bold = True
        .Font.Color = vbRed
    End With
End With
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

this does not work in the loop

There is no loop. Perhaps you have not shown enough of your code and have not explained in words what you have and what you are trying to achieve.

 

[sr1111]

I found a solution:

Sub try()
 Dim c As Range
 Dim lRow As Long
 lRow = 1
 Dim lRowLast As Long
 Dim bFound As Boolean
 With ActiveSheet
  lRowLast = .Cells(.Rows.Count, 1).End(xlUp).Row
  Do
   Set c = .Range("A" & lRow)
   If c.Value Like "*COLLECTION*" Then
    bFound = True
   ElseIf bFound Then
    bFound = False
    If c.Value <> "BALANCE" Then
     c.EntireRow.Insert
     lRowLast = lRowLast + 1
     c.Offset(-1, 0).Value = "BALANCE"
     c.Offset(-1, 0).Font.Color = RGB(0, 0, 0)
    End If
   End If
   lRow = lRow + 1
  Loop While lRow <= lRowLast + 1
 End With
End Sub

 

[Peter_SSs]

I found a solution:

Thanks for letting us know.

BTW, it is CODE tags you need to use, not QUOTE tags when posting vba code. I have fixed it again for you.

 

[sr1111]

Thanks for letting us know.

BTW, it is CODE tags you need to use, not QUOTE tags when posting vba code. I have fixed it again for you.

Modifying this code "how can I add a row with a certain keyword before a key of interest in a row". Just curious.

 

[sr1111]

How can I put a row above instead of below by changing the VBA above?

 

[bebo021999]

Maybe:

Option Explicit
Sub try()
Dim lRowLast&, i&, rng, f, u, ad As String
lRowLast = Cells(Rows.Count, 1).End(xlUp).Row

' get union of rows match
'find 1st match
Set f = Range("A1:A" & lRowLast).Find("*COLLECTION*")
If Not f Is Nothing And f.Offset(-1, 0).Value <> "BALANCE" Then ' if match and upper cell value <> "BALANCE"
    Set u = f
    ad = f.Address
    Do
        Set f = Range("A1:A" & lRowLast).FindNext(f) ' find next match
        If Not f Is Nothing And f.Offset(-1, 0).Value <> "BALANCE" Then Set u = Union(u, f)
    Loop Until f.Address = ad
u.EntireRow.Insert
End If
 
' find match then add text
lRowLast = Cells(Rows.Count, 1).End(xlUp).Row
Set f = Range("A1:A" & lRowLast).Find("*COLLECTION*")
ad = f.Address
If Not f Is Nothing And f.Offset(-1, 0).Value = "" Then
    With f.Offset(-1, 0)
        .Value = "BALANCE"
        .Font.Color = RGB(0, 0, 0)
    End With
    Do
        Set f = Range("A1:A" & lRowLast).FindNext(f)
        If Not f Is Nothing And f.Offset(-1, 0).Value = "" Then
            With f.Offset(-1, 0)
                .Value = "BALANCE"
                .Font.Color = RGB(0, 0, 0)
            End With
        End If
    Loop Until f.Address = ad
End If
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel macro to insert row text in above cell if searched key found in below cells
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.