Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Search and Replace macro for microsoft excel [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
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.

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 766
You can use Lookup to do that


example
=VLOOKUP(A2,[Book1.xls]Sheet1!$A:$T,5, false)
Report

Not sure how that replaces my address with the ones in the other workbook.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 766
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!