How to find and replace a word in a sentence with VBA

Hi you all
I have a table where I have two columns and in one of them there are names and in the other there should be numbers. The problem is that in the name column there are sometimes just the last name and sometimes the full name including title. I want to enter a specified number in column 2 if the name in column 1 contains "Moore". That would include Mr Simon Moore or Moore or MR Moore or Simon Moore.
I know it is possible, but I think I was sick the day we learned how to do it. Can you please help.
Gunilla

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Jeff,

You're not changing the value of c, so if What is found once, it will be found forever. However, if you were -- for example deleting the contents of c -- then this line will eventually generate an error:

Please Login or Register  to view this content.

... because once c is Nothing, c.Address generates an Object variable or With block variable not set error, because VBA evaluates all arguments of a logical expression.

Try this demo workbook

It finds "Moore" in column A and adds 123 to Column B when found