Macro for seaching a phrase and add 7 rows after that phrase

Dear all,

I have a question and hope someone could help save me from it.

I need to create a macro to find the phrase "I Love You" in an excel worksheet column B.
However, this seleceted phrase always appear in different rows depending on the month.

After the macro helped seaching the phrase "I Love You", i need to insert 7additional rows below it.


In conclusion, is there anyone that knows the macro to search the phrase "I Love You" in column B (but the row that the phrase will be located in differ).
after searching the phrase, the macro would also help adding 7 rows below it.

thank you very much in advance.

Select range (or column) and run this macro:

It will prompt you for phrase so you can type whatever and then run.

Please Login or Register  to view this content.

It will end after first match.
If you want to add 7 rows after each match remove "Exit Sub" part

hi zbor,

Thank you so much for you help.
i tried running the macro, however, it is able to find the phrase i am looking for when the macro prompt me.

when you said "select range or column and this run macro" what do you mean by that?
sorry that i am totally new to macros

sorry and thank you.

Select range.. Like put pointer on some cell, click left button on the mouse and drag down...
So cells become blue. You can select also whole (B) column by pressing B at the top of the sheet

pattern-dragged-down.png

Then run macro.

thank you guys!

your macro work wonders, like magic. its my first time doing macro though. i am so amazed by it
by the way, the 7 rows that is added in after i run the macro will only add to the particular column below the phase.
i need the rows to add across worksheet.
i think this is becaused my worksheet has merged cells.

are there any ways that i could add the rows across the worksheet and not affecting the merged cells below?

thank you so much my saviour.

i had unmerged the cells. however, the rows still do not add acorss the whole worsheet but only added below the column with my selected phrase

Use this for entire row:

Please Login or Register  to view this content.

Another:

Please Login or Register  to view this content.

Thank you guys so much, i tried the macros and all of them worked wonders.

I managed to insert 7 rows successfully thanks to your help!

I'm currently using the code provided by John H. Davis as it is less complicated for a newbie like me, however, the other codes worked well too, i've tried them already

Thank you all once again for all the quick response!