Find & Replace To Match Whole Word Only - VBA Adjustment

[PeeterP]

Hi i found the macro online and did little changes to it, it works great, but i need an adjustment so that it will match whole word only.

Here is my VBA:

Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to your table
  Set tbl = Worksheets("List Of Locations To Rmove").ListObjects("Table1")

'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
  
'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
        If sht.Name = "KWs Cleaning Sheet" Then
          
          sht.Cells.Replace What:=" " & myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        
        End If
      Next sht
  Next x

End Sub

If i try to replace word "put" by word "insert" it will also turn word "input" into word "inreplace" and that's what i want to avoid by applying match only whole word function which for some reason isn't included in excel.

Anyone of you excel wizards know how to tweak my vba to make it work that way?

 

[Snakehips]

Thanks a lot Snakehips, it works exactly the way i wanted!

You are welcome. Glad it helped.

 

[PeeterP]

Hi Tony, would you help me out once more please? I've found an issue with your code, it works great except, for some reason it doesn't replace a word that was in a cell where some other word was already replaced, what i mean by that is this:

For example I have 3 cells like this :
car insurance san jose ca
car insurance san jose
car insurance ca

I will run the VBA and i will have this 2 locations to be removed:
ca
san jose

As a result my 3 cells will look like this
car insurance san jose
car insurance san jose
car insurance

It looks like it fails to edit the same cell twice or something like that. Would you help me to sort this out please?

 

[Snakehips]

Peeter,

The code I gave in order to replace 'whole words' uses the Split function with the space " " being the delimiter or split point.

Therefore it is seeing 'san jose' as separate words not a discrete place name.

It will work if you put ca, san & jose as separate words / rows in your table of words to replace.

Hope that helps.

 

[PeeterP]

OK that makes sense, thanks again.

 

[Decker72]

HI SnakeHips I have tried this but for some reason I kep getting a subscript out of range at Set tbl = Worksheets("Sheet3").ListObjects("Table1"). I have tried to make the sheet suitable by changing sheet and table names but cannot seem to get rid of the subscript error, I also tried to look at the object list properties but getting a little lost! What am I doing wrong? Thank you

 

[Decker72]

Oops I made an error on in the table, I think I have it Thanks