rewriting a cell's contents within a range of cells

[GS7clw]

I am trying to create a macro that will scan a range of cells
and replace the (text) contents of the cell based on keywords
within the cell. For example, if a cell contains:

APPROVED MED WAIVER INSPECT & ENLIST TEAM GAMBIT

i want it to replace this string with INSPECT & ENLIST

The following code will scan through the correct range of cells.
it includes the key words I want to search for. Also, it rewrites
the cell contents BUT it is only putting the & in the cells.
It is not putting the key words back into the cell contents.

Please Login or Register  to view this content.

ANY ideas or help would be appreciated.
Hope everyone has a great weekend (tomorrow is FRIDAY!!!).

cliff

[ChemistB]

When you go to your cases, i.e. Case 2, you only set up the string as Firstword & Secondword whereas, your actual value might have only 3rd and 4th word. A better way to do it is to concatenate as you go along. Try this

Please Login or Register  to view this content.

Does that work for you?

[GS7clw]

Fantastic and the way you have done it I can add and subtract words as needed.

Excel locked up after running this macro but I just changed L:L to L1:200 to stop it running.
(Will never have even close to 200 so it works great AND the way you did it I can add words as needed.

THANK YOU!!!!

Have a great weekend
cliff