Find and replace whole cell by substring

[CaptainCsaba]

Hi!

I have a file in which there are a lot of company names in column A. They are not perfect so a lot of them have to be corrected by find and replace. I created another workbook called "namechanges.xlsx". In this file column A contains the substrings that have to be matched in the other files strings, and column B contains the replacement text. I have managed to find a code that almost works, however I think it uses the whole cells as comparison and is nto trying to match the substrings in the cells and thus there is no match. How can we fix this?

Sub FindAndReplaceing()    Dim NameListWB As Workbook, thisWb As Workbook
    Dim NameListWS As Worksheet, thisWs As Worksheet
    Dim i As Long, lRow As Long


    '~~> This is the workbook from where your code is running
    Set thisWb = ThisWorkbook
    '~~> Change this to the sheet name where you want to replace
    '~~> in Column A
    Set thisWs = thisWb.Sheets("Sheet1")


    '~~> File.xlsx
    Set NameListWB = Workbooks.Open("C:\Users\xyz\namechanges.xlsx")
    Set NameListWS = NameListWB.Worksheets("Sheet1")


    With NameListWS
        '~~> Find last row in Col A of File.xlsx
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row


        '~~> Loop though Col A
        For i = 1 To lRow
            '~~> Do the replace
            thisWs.Columns(1).replace What:=.Range("A" & i).Value, _
                                      Replacement:=.Range("B" & i).Value, _
                                      SearchOrder:=xlByColumns, _
                                      MatchCase:=False
        Next i
    End With
End Sub

 

[CaptainCsaba]

Only the first one is working, The second does not for some reason.

 

[Fluff]

Using this

            thisWs.Columns(1).Replace "*" & .Range("A" & i).Value & "*", .Range("B" & i).Value, xlPart, , False, , False, False

Should work, because it will change the entire contents of the cell

 

[CaptainCsaba]

Yes it does! It works wonderfully now! Thank you for taking the patience and for figuring this one out. You just helped us not doing a days work every month. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback