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
13
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top