Search - Find - Replace

[donaldv]

Hi,

I have the following situation:

In cell A1 to A10 there are names like London, Chicago, Brussels. In the cells B1 to B10 there is a code corresponding to each word. So London corresponds with 1, Chicago with 2, Brussels with 3 etc ...

I have a list from F1 to F100 where the cities are listed randomly. I need to search through the entire list from F1 to F100 and replace each word with the corresponding code.

Can I run this in a macro??

Problem is that the word in the cells A1 to A10 can change, also the corresponding code can change. So I always need to work with the cell number and never with the cel value.

Thanks for your help,

 

[SteveO59L]

Have a look at vlookup()

 

[texasalynn]

Welcome to MrExcel Board....

you don't really need a macro, vlookup will do that for you

=vlookup(F1,A1:B100,2,false)
adjust the table A1:B100 to match your area
then copy the formula down

 

[VoG]

With a macro

Sub rplc()
Dim i As Long, Found As Range
For i = 1 To 100
    Set Found = Range("A1:A10").Find(what:=Range("F" & i).Value, lookat:=xlWhole)
    If Not Found Is Nothing Then Range("F" & i).Value = Found.Offset(, 1)
Next i
End Sub