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



## gravanoc (Dec 18, 2022)

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 (Dec 18, 2022)

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.xlsmAB5Best Choice RealtyBest Choice Realty632160Sheet2 (2)Cell FormulasRangeFormulaA6:B6A6=CODE(MID(A5,5,1))


----------



## gravanoc (Dec 18, 2022)

Peter_SSs said:


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


Thanks, I will give it a try.


----------

