Macro Search and Replace

[raymachan]

Our IT team was tasked in creating a Macro tool for Microsoft Excel
2003 that we had in our Word application but faced some problems in
executing the tool.

Below are the problems that were encountered:

a. Replace function limitations. If the cell has more than 180
characters, the program failed to replace the data.

b. Multiple replacements of data in one cell failed.

c. Changing of color for replace characters also failed.

The tool was to be customized to help our Editing team in polishing a
file that's been sent by our clients.

Any suggestions that could help us improve or speed up our process
will be truely appreciated.

Thanks,
Raymond

 

[Joel]

Post code so we can look at it. there arre a few methods that you may be
using and without knowing the methods used in the code it is hard to give an
answer.

 

[Dave Peterson]

a. Change as many cells as possible with the edit|replace equivalent. But then
look for the cells that still have the old value and use VBA's replace to get
those (those long cells).

Remember to look at only the cells that are values (non-formulas) and are text
(not numeric).

b. I don't have a guess.

c. You'll have to loop through each found cell and change the strings and
colors character-by-character.