I have four columns in excel.All numbers.

column A ---1,2,3,4,
column B ---123,351,654,896
column C --- 2,4,1,3
column D ---1,4,3,2

column C and D have the same numbers as column A but in a differen
sequence.

I want the macro to do the following
==========================
start with column C.If picks up the first number 2, searches for tha
number in Column A, find the corresponding number in column B( 351 i
this case) replaces the number 2 in column C with 351.

then it goes to the second number of column C (4 in this ) looks fo
this number in column A, finds the corresponding number in column B(89
in this case), replaces the number 4 in column C with 896

So Column A and colums B are for referring and the columns C and D ar
where the replace occurs.

Please give me a general macro so that if I have an additional columnD
I can perform replace on Column D as well.In the above example we hav
four rows, what if I have 4000 rows.

thank

--
Message posted from http://www.ExcelForum.com

Try the following macro

To apply the macro to column D, just change the "Columns(3)" in the 4th line to "Columns(4)"

'---------------------------------------
Sub finder(
Dim cell As Object, c As Rang
With Columns(1
For Each cell In Application.Intersect(Columns(3), ActiveSheet.UsedRange).Cell
If cell.Value < "" The
Set c = .Find(cell.Value, LookIn:=xlValues
If Not c Is Nothing The
cell.Value = c.Offset(0, 1).Valu
End I
End I
Nex
End Wit
End Su
'---------------------------------------

Regards
Edwin Ta

http://www.vonixx.co


----- excelguru wrote: ----

I have four columns in excel.All numbers

column A ---1,2,3,4
column B ---123,351,654,89
column C --- 2,4,1,
column D ---1,4,3,

column C and D have the same numbers as column A but in a differen
sequence

I want the macro to do the followin
==========================
start with column C.If picks up the first number 2, searches for tha
number in Column A, find the corresponding number in column B( 351 i
this case) replaces the number 2 in column C with 351

then it goes to the second number of column C (4 in this ) looks fo
this number in column A, finds the corresponding number in column B(89
in this case), replaces the number 4 in column C with 89

So Column A and colums B are for referring and the columns C and D ar
where the replace occurs

Please give me a general macro so that if I have an additional columnD
I can perform replace on Column D as well.In the above example we hav
four rows, what if I have 4000 rows

thank


--
Message posted from http://www.ExcelForum.com

Thank-You!!! for the macro works beautifully.

One more request for the same problem
============================

suppose all the values in column C do not match with those in colum
A.

can the script write out those values from column C , which it did no
find in column A , into another column G.

Thanks for all the help.

--
Message posted from http://www.ExcelForum.com

Sure! Try the new macro below

'---------------------------------------------------------------
Sub finder(
Dim cell As Object, c As Range, counter As Singl
counter =
With Columns(1
For Each cell In Application.Intersect(Columns(3), ActiveSheet.UsedRange).Cell
If cell.Value < "" The
Set c = .Find(cell.Value, LookIn:=xlValues
If Not c Is Nothing The
cell.Value = c.Offset(0, 1).Valu
Els
Columns(7).Cells(1).Offset(counter, 0).Value = cell.Valu
counter = counter +
End I
End I
Nex
End Wit
End Su
'---------------------------------------------------------------

Regards
Edwin Ta

http://www.vonixx.co


----- excelguru wrote: ----

Thank-You!!! for the macro works beautifully

One more request for the same proble
===========================

suppose all the values in column C do not match with those in colum
A

can the script write out those values from column C , which it did no
find in column A , into another column G

Thanks for all the help.


--
Message posted from http://www.ExcelForum.com