Find and replace text

[leimst]

Hello,

I have never used VBA before so I am totally unfamiliar. I am looking for VBA code that would find certain text and replace it using a reference table so that the code knows what to replace the original text with. I have attached an example doc where the "Reference" tab provides a table showing the original text and what it should be replaced with.

Thank you in advance for any assistance!

leimst

 

[sgmpatnaik]

@leimst

Try the below code

Sub Evaluate_Data()
Dim i, j, LastRowSh1, LastRowSh2
   LastRowSh2 = Sheets("Reference").Range("A" & Rows.Count).End(xlUp).Row
   LastRowSh1 = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRowSh2
For j = 1 To LastRowSh1
       If Sheets("Data").Cells(i, "A").Value = Sheets("Reference").Cells(j, "A") Then
       Sheets("Data").Cells(i, "A").Value = Sheets("Reference").Cells(j, "B").Value
       End If
Next j
Next i
End Sub

Hope it clear if any problem please inform

Thanks

 

[jeffreyweir]

It would be more efficient to use VBA's .Replace method and do all the cells at once, rather than iterating throgh them one at a time.

I don't have time to code anything up, but here's what the Macro recorder spits out as a basis:

Cells.Replace What:="Something", Replacement:="Something Else", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[leimst]

Thank you Patnaik, that worked great!

leimst

 

[sgmpatnaik]

Glad It Worked