convert codes to their name

hanasamo

Board Regular
Joined
May 31, 2005
Messages
83
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi

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

Code:
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
 
Upvote 0
Hi

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

Tony
 
Upvote 0
acw said:
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.
 
Upvote 0

Forum statistics

Threads
1,212,046
Messages
6,105,585
Members
447,972
Latest member
carrieann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top