Find and replace module help

[joeny0706]

Hi All
I have a module that looks at an excel sheet and use that asa find and replace. I have been using this for over a year and works great. Ireceived some new data I need to convert but this has a lot of cells that havesome of the same text so it replaces only half. I am not sure if I amexplaining so below is examples



HB PRICE CHOPPER 1

1PriceChopper:Pch001 Eastern Parkway




HB PRICE CHOPPER 139

1PriceChopper:Pch139 Amsterdam





The column on the left is that data it finds then itreplaces it with the column on the right. But sometimes it puts two of them together.


But when it finds HB PRICE CHOPPER 139 it is converting without looking at all the contentsof the cell..


HB PRICE CHOPPER 139
It is turning the above into this



1PriceChopper:Pch001 Eastern Parkway39
Rather than what it should be
1PriceChopper:Pch139 Amsterdam



How can I make the module so all the contents of the cell must match?

So it does not stop until it analyzes all the contents of the cell.






The module code is in the attached word doc. Also the excel sheet with the find and replace data. I did not have any luck finding the answerso next was to see if anyone could help me out.




Thanks ALL

[Fluff]

If you want to match the entire cell content use LookAt:=XlWhole rather than XlPart

[p45cal]

If you have to use xlPart, then it's finding HB PRICE CHOPPER 1 in HB PRICE CHOPPER 139 before it finds HB PRICE CHOPPER 139.
Sorting your pairs of columns by length of the first column, largest first, should help by looking for PRICE CHOPPER 139 before looking for PRICE CHOPPER 1.

[joeny0706]

If you have to use xlPart, then it's finding HB PRICE CHOPPER 1 in HB PRICE CHOPPER 139 before it finds HB PRICE CHOPPER 139.
Sorting your pairs of columns by length of the first column, largest first, should help by looking for PRICE CHOPPER 139 before looking for PRICE CHOPPER 1.

I don't have any reason that I know of requiring me to use xLPart. Unless is causes any issues. I will try LookAt:=XlWhole rather than XlPart an see if that works.

Thank both


Is thee anwayto disable ato save when typing in this forum.It causes isses when I type


I will try that now andlet you know

zthasnk

[joeny0706]

Thanks all

Fluff that worked perfect.
Thanks

[Fluff]

You're welcome & thanks for the feedback