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

hikerguy

New Member
Joined
Sep 11, 2014
Messages
8
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,691
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,638
Office Version
365
Platform
Windows
How about
Code:
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

New Member
Joined
Sep 11, 2014
Messages
8
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

New Member
Joined
Sep 11, 2014
Messages
8
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,638
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,085,314
Messages
5,382,923
Members
401,809
Latest member
jbeanx80

Some videos you may like

This Week's Hot Topics

Top