"Find and Replace" macro finds items nested in larger words!

[IrishMist1748]

Hello!

I am using the following macro to auto find and replace multiple items at once with Sheet2 containing the items I want to "find and replace" (with 'finds' in column A and 'replaces' in column B) and Sheet1-column B is the text I am running the "find and replace" macro against:

Sub FindReplace()
Dim i As Integer
Dim FindStr As String
Dim RepStr As String
For i = 1 To 145
    FindStr = Sheet2.Range("A" & i).Value
    RepStr = Sheet2.Range("B" & i).Value
Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=" RepStr"
Next i
End Sub

My problem/question is how do I change the code so it will only "find" whole "words" and do the appropriate replacement? Currently it is finding "c" mixed amongst other words instead of finding 'c' by itself.

Example:
Find: C, Replace with: complete
Find: CL, replace with: clear

Text searched: CL C Clock

Current results: completeL complete completelo completek
Desired result: clear complete clock

Thank you!
<table x:str="" style="border-collapse: collapse; width: 368pt;" width="490" border="0" cellpadding="0" cellspacing="0"><col style="width: 368pt;" width="490"><tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 368pt;" width="490" height="17">
</td> </tr></tbody></table>

 

[imimin]

Thanks jonmo! Sorry for bumping this so long after the fact-I came back to it and found a couple of questions. This works well:

You could do something like this

    For i = 1 To 145
        Select Case Range("A" & i).Value
            Case "14ky", "another exception", "something else"
                FindStr = S2.Range("A" & i).value & " "
                RepStr = S2.Range("B" & i).Value & " "
            Case Else
                FindStr = " " & S2.Range("A" & i).Value & " "
                RepStr = " " & S2.Range("B" & i).Value & " "
        End Select
        S1.Range("B:B").Cells.Replace What:=FindStr, Replacement:= RepStr, LookAt:=xlPart
    Next i

I just have 2 more questions. Now your full code looks like this and works well:

Sub FindReplaceOriginal()
Dim i As Integer, FindStr As String, RepStr As String, LR As Long, S1 As Worksheet, S2 As Worksheet
Set S1 = Worksheets("Sheet1")
Set S2 = Sheet2
LR = S1.Cells(Rows.Count, "B").End(xlUp).Row
With S1.Range("C1:C" & LR)
    .Formula = "= "" "" & B1 & "" """
    .Copy
    S1.Range("B1").PasteSpecial xlPasteValues
 
    For i = 1 To 262
        Select Case Range("A" & i).Value
            Case "14ky", "another exception", "something else"
                FindStr = S2.Range("A" & i).Value & " "
                RepStr = S2.Range("B" & i).Value & " "
            Case Else
                FindStr = " " & S2.Range("A" & i).Value & " "
                RepStr = " " & S2.Range("B" & i).Value & " "
        End Select
        S1.Range("B:B").Cells.Replace What:=FindStr, Replacement:=RepStr, LookAt:=xlPart
    Next i
    '.Formula = "=TRIM(B1)"
    '.Copy
    'S1.Range("B1").PasteSpecial xlPasteValues
    '.ClearConents
End With
End Sub

My question is, is there a way to write the code in a way that when it sees numbers, a '/' or a '-' on sheet 1 when doing the find-replace that it would ignore them?

Example of this would be if it sees '14KWHT' it would ignore the 14 and replace WHT with 'carat white gold' the output in that case would be '14 carat white gold"? Another example would be if it sees 'BGT/PRIN' it would ignore the '/' and replace BGT with 'baguette' and 'PRIN' with 'princess' for a final output for that item of 'baguette princess'?

THANK YOU FOR YOUR CONTINUED HELP!

 

[imimin]

I guess what I am asking if there is a way to put some restrictions on the line of code that reads:

S1.Range("B:B").Cells.Replace What:=FindStr, Replacement:=RepStr, LookAt:=xlPart

so it will skip the numerics, '/' and '-'?

Thanks!

 

[imimin]

Anyone on this?