On Sep 9, 10:36*pm, ward376 wrote:
Sub way()
Dim strNm As String
Dim rngData As Range
Dim cel As Range
Dim rngReplacement As Range
Dim strReplacement As String

'change to the range where your data is
Set rngData = Sheet1.UsedRange

'change to the range where your conversion table is
Set rngReplacement = Sheet2.UsedRange

For Each cel In rngData

strNm = cel.Value

On Error Resume Next
If cel.Value < "" Then
* * strReplacement = _
* * * * rngReplacement.Find( _
* * * * What:=strNm, _
* * * * After:=Sheet2.Range("a1"), _
* * * * LookIn:=xlFormulas, _
* * * * LookAt:=xlWhole, _
* * * * SearchOrder:=xlByRows, _
* * * * SearchDirection:=xlNext, _
* * * * MatchCase:=True, _
* * * * SearchFormat:=False) _
* * * * .Offset(0, 1).Value
If strReplacement < "" Then _
* * cel.Replace _
* * * * What:=strNm, _
* * * * replacement:=strReplacement, _
* * * * LookAt:=xlWhole, _
* * * * SearchOrder:=xlByRows, _
* * * * MatchCase:=True, _
* * * * SearchFormat:=False, _
* * * * ReplaceFormat:=False
End If
Next cel

On Error GoTo 0

End Sub

Cliff Edwards

Edward,

The code replaces but has two bugs:

- It is replacing every used cell in sheet
- The code replacement is case sensitive

Pls ammend & revert.

Thx