Excel - Macro to search and replace

October 2017




Issue


I have two workbooks with identical setup.

item name address 1 address 2 address 3
x 12 34 56 


I need a macro to search for "x" in the other workbook and replace address 1, 2 and 3 with the addresses in that other workbook. I also need the macro to continue to do this for every item listed in the "item name" column.

Solution


Let's say you do if in E, F and G. This gives you address from other book. Then you can use copy and paste special to make it value


then you can delete original b, c and d

you would be using something like this


=IF(ISERROR(VLOOKUP(A2,[Book1.xls]Sheet1!$A:$D,2, false)), A2,VLOOKUP(A2,[Book1.xls]Sheet1!$A:$D,2, false)) 


which basically says if the value is not found in book2, then keep original value else get the updated value.

Note


Thanks to rizvisa1 for this tip on the forum.
Published by aakai1056. Latest update on May 12, 2010 at 05:50 AM by aakai1056.
This document, titled "Excel - Macro to search and replace," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).