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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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
36,150
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
36,150
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,480
Messages
5,414,789
Members
403,544
Latest member
rabdorawsh

This Week's Hot Topics

Top