Search - Find - Replace

donaldv

New Member
Joined
Mar 30, 2009
Messages
1
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,
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Legend
Joined
Jun 19, 2002
Messages
63,651
With a macro

Code:
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
 

Forum statistics

Threads
1,082,573
Messages
5,366,372
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top