"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>

 

[Jonmo1]

Try

Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=" RepStr", LookAt:=xlWhole

 

[Andrew Poulsom]

Try:

Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=" RepStr", LookAt:=xlWhole
Next

 

[IrishMist1748]

Thanks guys!

I tried what you suggested by adding ', LookAt:=x1Whole' at the end of line 9, making my code look like:

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, RepStr", LookAt:=xlWhole
Next i
End Sub

but it returns a syntax error! Can you help me with this?

Thank you!

NOTE: I cannot use the 'Replacement:=" RepStr"' syntax as it does some funny things with my output printing a bunch of 'Rep' words!

 

[Andrew Poulsom]

Try:

Worksheets("Sheet1").Range("B:B").Cells.Replace What:=FindStr, Replacement:=" " & RepStr, LookAt:=xlWhole

 

[IrishMist1748]

When I run the macro with this code, NOTHING HAPPENS! I press 'run' and no changes! But when I close the Excel window, it asks if I want to save changes?

 

[Andrew Poulsom]

Are you sure that the whole word in FindStr exists in column B?

 

[IrishMist1748]

This is what my tables "look" like now:

Sheet2
A B
C complete
CL clear


Sheet1
Column A Column B
CL C Clock CL C Clock

Sorry, I could make this any more clear. Would be nice if you could put samples of spreed sheets up!

 

[Jonmo1]

Sorry, I could make this any more clear. Would be nice if you could put samples of spreed sheets up!

See my signature

 

[Andrew Poulsom]

You need xlPart, but whole words only within the text. So you would need to find "C ", " C", " C ", "CL ", " CL" and " CL " etc.