Previous Topic Next Topic

Search and Replace macro for microsoft excel

Solved/Closed
Doyle1010 - May 11, 2010 at 01:01 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 11, 2010 at 10:20 PM
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.
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 11, 2010 at 04:30 PM
You can use Lookup to do that


example
=VLOOKUP(A2,[Book1.xls]Sheet1!$A:$T,5, false)
0
Doyle1010
May 11, 2010 at 05:03 PM
Not sure how that replaces my address with the ones in the other workbook.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 11, 2010 at 10:20 PM
Lets 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 some thing 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
0