Replacing many values

[David Gilbert]

Hi

I have a large amount of changes I need to make in an Excel workbook. This is changing codes to something legible for instance:

HYT564 = SCANNER

I can obviously use find and replace but this would be a long process since there are about 80 codes that need changing and this process needs to be done every month. Is there any way you can write a macro and then run this across the whole sheet so that it replace all the codes? If so I would appreciate help of how this would be wriiten.

Thanks
David

[contaminated]

Can u not use wildcards if all codes starts with HYT... or even H
Like

In find what field type HYT* or H* if they all starts with H and replace 'em with anything u want.

[martindwilson]

this should do it
replaces stuff in column A from a list in B and C (B=old C=new)

Please Login or Register  to view this content.

if however your cells to change arent in one row but across a range on sheet you can change it and your find replace list so suit
eg range is in cols a,b,c,d and replace list list is in cols E/F
then

Please Login or Register  to view this content.

note this is not written for excel 2007 but as long as you dont go above 65536 rows it should work(i'm just of to my other machine to test it! and it does )

place code in a new module and run from tools/macro/macros
for 2007 thats
developer tab/macros (if you cant see developer tab )
office button/excel options and in the top options for working with excel pane check "show developer tab in ribbon" option
see link my signature for where to put code

[David Gilbert]

Hi Martin

thanks for the reply, I am very impressed with the speedy and thorough answer! However at the risk of being irritating can you please explain exactly where the items for changing should be entered? For example here are 2 of the example codes that need switching:

HRDW-SWTC Switch*
MSCL-BSBL Base Builds*

I am not used to writing macros and so would appreciate if you could spell where they need to go exactly in the code.

By the way it would be your second solution since they are likely to be scattered around the sheet.

Thanks for your time, it is appreciated.

David

[martindwilson]

in the second example i used column e but you can change that to any spare column,(thats two columns one to hold old value,and one next to it,on the right to hold new)
in column e put old value in column f put corresponding new value
the macro will search each cell in a:d (also changable)
thats this bit

Please Login or Register  to view this content.

and look for the first value in col e
thats this part

Please Login or Register  to view this content.

(E1)if it finds it it will replace it with that in column f (F1).
this bit says

Please Login or Register  to view this content.

use the value 1 column to the right,
it then repeats the process searching for second value in column e (E2) and if found replace with value in col f (f2)