Replacing Values With Another Sheet's Value

[mmertt900]

Hi Everybody,

I have a question for you. I have a workbook with two sheets. First one is main sheet with all the data and second one is kind of a description sheet. In the first sheet there are data's specify places but these are codes. For example SHANGHAİ comes as CNSHA. İn second sheet I define these places like below. What I want to do is loop through places in first sheet and replace them with second sheet's value. I've tried below code but it is not effective. I need your help.

First Sheet 'lets say A1	First Sheet 'lets say B2	Second Sheet ' lets say A1	Second Sheet 'lets say A2
'some data	CNSHA	CNSHA	SHANGHAİ

'some data	KRPUS	JPTYO	TOKYO
'some data	JPTYO	KRPUS	BUSAN

Dim Rng as Range

For Each Rng in Range("B2:B"&cell(rows.count,2).end(xlup).row)
Rng.Value=Sheets(2).cells.find(rng.value).offset(0,1).value
Next Rng

 

[Fluff]

What columns in sheet2 has the abbreviation & Name?
Also hows many rows of data do you have on each sheet?

 

[mmertt900]

What columns in sheet2 has the abbreviation & Name?
Also hows many rows of data do you have on each sheet?

İn Sheet2 Column A has the abbrevation and Column B has the description.
First sheet(main sheet) has to be dynamic because I consolidate data's every week. But second sheet (Abbrevation Sheet) has almost 60 rows.

 

[Fluff]

Ok, how about

Sub mmertt()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet2")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
   End With
   With Sheets("Sheet1")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then Cl.Value = Dic(Cl.Value)
      Next Cl
   End With
End Sub

Change sheet names to suit

 

[mmertt900]

Ok, how about

Sub mmertt()
   Dim Cl As Range
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet2")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
   End With
   With Sheets("Sheet1")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then Cl.Value = Dic(Cl.Value)
      Next Cl
   End With
End Sub

Change sheet names to suit

Oh man that's what I'm looking for. Thank you so much for your help.

 

[Fluff]

You're welcome & thanks for the feedback.