convert codes to their name

[hanasamo]

I have a worksheet called "city code" in which the column A contains codes such as "PEK", "SHA", or "SFO", every code represents a city. And in column B, there are city names correspondent to those codes. There are up to 4000 rows.

And I have another worksheet contains a report for flight routes, there are "pek-sha", "sha-sfo","pek-sha－sfo" and all things like that. I want to design a macro that can convert all codes to city name, how can I do this?

Thanks in advance

 

[acw]

Hi

This assumes:
1) Data in City Code starts in A2
2) sheet3 column A contains the codes you want to convert.

Sub bbb()
 Sheets("sheet3").Select
 For Each ce In Sheets("city code").Range("a2:a" & Sheets("city code").Cells(Rows.Count, "A").End(xlUp).Row)
  Range("a:a").Replace what:=ce, replacement:=ce.Offset(0, 1)
 Next ce
End Sub

HTH

Tony

 

[hanasamo]

Thank you!

I copy and paste your code to my vba, and run it, but nothing changed.

 

[acw]

Hi

1) Where did you paste it.
2) Did you update sheet3 to the correct sheet name.

Tony

 

[hanasamo]

Hi

1) Where did you paste it.
2) Did you update sheet3 to the correct sheet name.

Tony

Yes， I did. What I want to convert is the codes in sheet3, but the sheet "city code" is converted. I'm new to VBA, I don't know how to edit the code you gave to me to suit my case.

 

[acw]

Hi

PM me with an email address and I'll send you a sample file.


Tony