How do I automate find and replace.

[ghrek]

Hi

I have a workbook that in column A on sheet 1 each row has different 3 letters.

On sheet 2 of the same workbook I have in column A the same 3 letters and in column B the meaning of the 3 letters. What Im trying to do is to get it to look on sheet 1 in column A and then find it on sheet 2 and replace data on sheet 1 with the meaning thats in column B

Lets say in column A on sheet 1 says BDY at various locations and then in sheet 2 column A = BDY and the adjacent data in the same row in column B says return I need all data that has BDY on sheet 1 replaced by return.

There will be numerous different options.

Any ideas?

 

[Fluff]

How about

Sub ghrek()
   Dim Ary As Variant
   Dim i As Long
   With Sheets("Sheet2")
      ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 1)).Value2
   End With
   For i = 1 To UBound(ary)
      Sheets("Sheet1").Range("A:A").Replace ary(i, 1), ary(i, 2), xlWhole, , False, , False, False
   Next i
End Sub

 

[ghrek]

Spot on many thanks.

 

[Fluff]

You're welcome & thanks for the feedback