Search for text, insert a row above when found, insert text in that new cell, then repeat. How????

[hikerguy]

This is probably an easy one for you gurus, but it has me stumped. What I want to do is search for the ! symbol. When it's found, insert a row above it and insert some text. Then go to the next occurrence of the ! symbol and repeat until the end.

I created a macro that would do this, BUT, the problem is, it didn't go looking for the next occurrence of the ! symbol. It kept starting the search where I placed my cursor before starting the macro.

As an example, here's what I have:

one
two
three
!
one
two
three
!
one
two
three
!
one
two
three
!

Here's what I want to end up with:

one
two
three
four
!
one
two
three
four
!
one
two
three
four
!
one
two
three
four
!


Here's what I ended up with each time I ran my macro:


one
two
three
four
four
four
four
four
!
one
two
three
!
one
two
three
!
one
two
three
!


Can someone tell me how to accomplish this?

Thanks,

Andy

 

[JoeMo]

You want the "some text" that gets inserted to be "four" regardless of the text in the cells above it, or you want it to fill a textual number sequence - like you've shown?

 

[Fluff]

How about

Sub hikerguy()
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Replace "!", "=xxx", xlWhole, , , , False, False
        .SpecialCells(xlFormulas, xlErrors).EntireRow.Insert
        .SpecialCells(xlBlanks).Value = "Four"
        .Replace "=xxx", "!", xlWhole, , , , False, False
    End With
End Sub

 

[hikerguy]

Joe,

I am needing to insert the same text above the ! every time. I just tested out Fluff's code and it works exactly how I needed it. Thanks for getting involved in this one anyway.

 

[hikerguy]

Fluf,

That's exactly what I was looking for. I'm working on some Cisco switch configs and need to insert the command "no shutdown" at the end of the config for each interface. This literally saved me at least two hours, not to mention the monotony of finding the end of each interface config, pressing ENTER, pasting in "no shutdown", repeating at least 30x
per switch (and I have four switches to do).

Many thanks!

Andy

 

[Fluff]

You're welcome & thanks for the feedback