highlight cells with words replaced

[amitsansanwal]

Hello everyone,

I am trying to find and replace names to characters in a large data file. I am using the following code for this purpose,

Please Login or Register  to view this content.

However I would like to know the numbers of replacements made and highlight the cells in which replacements were made. Can anyone please help. Also is it possible to have rows with replaced cells copied to another worksheet?

Thanks,

Amit

[PCI]

Welcome amitsansanwal,
You certainly read the rules and you know you have to use code tags when posting code.
Can you attach a short sample of your data to help to update the macro.
PCI

[amitsansanwal]

Thanks PCI, nest time I will definitely use code tags.

My data is fairly simple, one columns has generic names, such as John, Jack etc and another has asterisk to replace the names with.

Third column has a lot of text in each cells, I have to replace names with asterisks in that column.

I have around 2000 names and more than 6000 text cells.

Wish I could upload the file but out network wont allow me.

Thanks,

Amit

[PCI]

"nest time I will definitely use code tags" you can do it now: Just Edit you thread and use

Please Login or Register  to view this content.

About your file you could reduce the amount of rows, it is just to a clear understanding of your data

[amitsansanwal]

Done, but I cannot uplode files.

[PCI]

"numbers of replacements made " for each word or only the full total?

[PCI]

See here a piece of code
Not sure it fits to what you need but it has been prepared from you previous code
Where to put the counter value ???

Please Login or Register  to view this content.

[amitsansanwal]

Thank you very much for doing this, i will try it out.

However I am also having issues with finding the whole worlds, for example, my find value is "Al" and I am replacing it with * then the the code replaces "al' with in "Pal" and it becomes P*. Whereas, I would like it to only replace the actual name "Al". This is happening when I am chosing xlPart

When I chose xlWhole then the code does nothing because the cell contains more test than just the name, for example it wont replace Al with * if it reads "Al is a good person" and not just Al.

Any help?

[PCI]

How do we know we have to replace when there is
"Pal" or "Al is a good person"
is it the space (before and after Al) which makes the difference?

[amitsansanwal]

Yes the space does make the difference.

[PCI]

After testing the file, it will be good you prepare a test file with some data like the one you shown to do some tests and update the macro.