What would prevent this Find & Replace operation in VBA from working?

[gravanoc]

On sheet 1 is a large table with various fields, including some office names. On sheet 2 is a list of office names I want to be replaced with an empty string. I've created a simple sub to carry this out, but nothing is happening. If I bring of the Find & Replace dialog after running the code, it will show the search term there, and if I try to F&R manually, it can't find anything. However, if I delete the search term and re-type it, then it will work. Any thoughts? Since I'm using wildcards, it seems like it should capture everything in the cell where the search term is present.

Sub FinderAndReplacer()

    Dim sTerm As String
    Dim rngA As Range
    Dim i As Long
    
    Set rngA = Sheet1.ListObjects(1).DataBodyRange.Cells
    Set rngB = Sheet2.Range("C4:C171")
    
    For i = 0 To rngB.Cells.Count - 1
        sTerm = rngB.Cells(i + 1, 1).Value
        sTerm = "*" & Mid(sTerm, 2, Len(sTerm) - 2) & "*"
        rngA.Replace What:=sTerm, Replacement:=""
    Next i
    
End Sub

Sheet 2 Office Names

Example of Office Names on Sheet 1

 

[Peter_SSs]

One thing to check for is leading or trailing spaces on the data from either sheet. Or perhaps that is what this is about?
Mid(sTerm, 2, Len(sTerm) - 2)

Another possibility is the the characters that appear as a space character are not the same on each sheet. For example, here A5 and B5 appear identical - but they are not as evidenced by the formula results in row 6.

22 12 18.xlsm
	A	B
5	Best Choice Realty	Best Choice Realty
6	32	160
Sheet2 (2)

Cell Formulas
Range	Formula
A6:B6	A6	=CODE(MID(A5,5,1))

 

[gravanoc]

One thing to check for is leading or trailing spaces on the data from either sheet. Or perhaps that is what this is about?
Mid(sTerm, 2, Len(sTerm) - 2)

Another possibility is the the characters that appear as a space character are not the same on each sheet. For example, here A5 and B5 appear identical - but they are not as evidenced by the formula results in row 6.

22 12 18.xlsm
	A	B
5	Best Choice Realty	Best Choice Realty
6	32	160
Sheet2 (2)

Cell Formulas
Range	Formula
A6:B6	A6	=CODE(MID(A5,5,1))

Thanks, I will give it a try.