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)
Doyle1010
May 11, 2010 at 05:03 PM
Not sure how that replaces my address with the ones in the other workbook.
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

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses