VBA Advice on find and replace whole word within a text string

[VBA learner ITG]

Hi all,

I need your guidance if possible.

The below code currently works by finding a part word from a table and amending part word to a what the user wants.

However when i amend the line of code to look at the whole word within a text string it doesnt perform the amendments.

Current Line of Code i amended: LookAt:=xlpart
New Line of Code which I have tried:to LookAt:=xlwhole

For example:

If i wanted to replace the word bottle to Bottle with a (capital B) so it looks like the below:

Current text string:Diet Coke 4 Pack Glass bottle
Required output: Diet Coke 4 Pack Glass Bottle

Dim wb As Workbook
Dim ws As Worksheet
Dim ws_client As Worksheet
Dim tbl As ListObject
Dim lrow As Range

Set wb = ActiveWorkbook
Set ws = wb.Sheets("MAIN BRIEF")
Set ws_client = wb.Sheets("CLIENT_FACING")

Set tbl = ws_client.ListObjects("Table1")
    For Each lrow In tbl.ListColumns(1).DataBodyRange.Rows
    find_str = lrow.Offset(0, 0)
    rep_str = lrow.Offset(0, 1)
    ws.Cells.Replace what:=find_str, Replacement:=rep_str, _
    LookAt:=xlpart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
Next lrow
Set tbl = Nothing
Set ws = Nothing
Set ws_client = Nothing
Set wb = Nothing
End Sub

Below is a table of words the above code is performing the find and replace:

WORD TO FIND	WORD TO REPLACE WITH IN THE SENTANCE
kitkat	KitKat
Dark Milk	Darkmilk
Ltrs	ltr
Bbq	BBQ
Co-Op	Co-op
Chocoalte	Chocolate
Rowntree'S	Rowntree's
Pastiles	Pastilles
Xl	XL
Muller	Müller
Nestle	Nestlé
Gu	Gü
McVities	McVitie’s
Crimbles	Mrs Crimbles
Chefs Cuisine	Chef’s Cuisine
Rowntrees	Rowntree’s
Mccain	McCain
Haagen Dazs	Haagen-Dazs
Walls	Wall’s
Youngs	Young’s
Ben & Jerrys	Ben & Jerry’s
Harriets Garden	Harriet’s Garden
Cheesestrings	Cheestrings
Emmi Caffe Latte	Emmi Caffé Latte
Rachels Organic	Rachel’s Organic
Jacksons	Jackson’s
sfc	SFC
Goodfellas	Goodfella’s
Moophoria	Moo-Phoria
Judes	Jude’s
Kellys	Kelly’s
Kitkat	KitKat
N/Valley	Nature Valley
NAS	No Added Sugar
4PK	4 Pack
Reg	Regular
S/Sations	Sensations
G/Wonder	Golden Wonder
Kit Kat	KitKat
T & Lyle	Tate & Lyle
J West	John West
Greengnt	Green Giant
L/GMan	Loyd Grossman
WUL	Wash Up Liquid
APA	Anti Perspirant Deodorant
APD	Anti Perspirant Deodorant
F/Berio	Filippo Berio
O.E. Paso	Old El Paso
U/Bens	Uncle Bens
F/Bentos	Fray Bentos
H/Hoops	Hula Hoops
Candylnd	Candyland
F/Shoot	Fruit Shoot
LS	Low Sugar
Robs	Robinsons
S/Cap	Sports Cap
Robs	Robinsons
S/Cap	Sports Cap

 

[VBA learner ITG]

Per the above I believe I have fixed the issue:

Dim wb As Workbook
Dim ws As Worksheet
Dim ws_client As Worksheet
Dim tbl As ListObject
Dim lrow As Range

Set wb = ActiveWorkbook
Set ws = wb.Sheets("MAIN BRIEF")
Set ws_client = wb.Sheets("CLIENT_FACING")

Set tbl = ws_client.ListObjects("Table1")
    For Each lrow In tbl.ListColumns(1).DataBodyRange.Rows
    find_str = lrow.Offset(0, 0)
    rep_str = lrow.Offset(0, 1)
    ws.Cells.Replace what:=find_str, Replacement:=rep_str, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
    SearchFormat:=False, ReplaceFormat:=False
Next lrow
Set tbl = Nothing
Set ws = Nothing
Set ws_client = Nothing
Set wb = Nothing
End Sub