Find and replace whole cell by substring

CaptainCsaba

Board Regular
Joined
Dec 8, 2017
Messages
78
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?

Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try
Code:
            thisWs.Columns(1).replace What:=.Range("A" & i).Value, _
                                      Replacement:=.Range("B" & i).Value, _
[COLOR=#ff0000]                                      LookAt:=xlPart _[/COLOR]
                                      SearchOrder:=xlByColumns, _
                                      MatchCase:=False
 
Upvote 0
Although that was the part I was looking for the cells still don't get replaced. The code runs for about 5 seconds then ends and nothing happens. I tried adding the sheet name to the end but that changed nothing.

Code:
Sub Sample()    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\csaba.hari\Desktop\webscrape\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:=NameListWS.Range("A" & i).Value, Replacement:=NameListWS.Range("B" & i).Value, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
        Next i
    End With
End Sub
 
Upvote 0
Do any of the values in col A of sheet1 in the namechanges workbook exist in the other sheet?
 
Upvote 0
Do any of the values in col A of sheet1 in the namechanges workbook exist in the other sheet?

Yes, not all but 90% of them should appear once or twice. To give you an example A40 in "namechanges" is "BREWIN DOLPHIN HLDGS", B40 is "BREWIN DOLPHIN HOLDINGS PLC" (this is what it needs to be changed to in the other worksheet. A1666 in the otherworksheet where the macro ran is "BREWIN DOLPHIN HLDGS PLC".

As you can see it should pick it up because it is a part of the other cell so A1666 should be changed to the B40 value. There are around 150 cells like this on average but nothing happens for some reason.
 
Upvote 0
Check for leading/trailing spaces & also check that you don't have any special/hidden characters
 
Upvote 0
I did a TRIM on all 3 columns and there are no special characters. Just regular letters, a dot and & here an there but not many. Still did not work.
 
Upvote 0
Something I had in mind that could be the problem is that the "namechanges" excel file gets opened halfway and maybe it is mistakenly checking the A column there, not in the other excel file where it should. Could this be the problem?
 
Upvote 0
Nope, that shouldn't be a problem.
Try
Code:
            ThisWs.Columns(1).Replace .Range("A" & i).Value, .Range("B" & i).Value, xlPart, , False, , False, False
and if that doesn't work
Code:
            ThisWs.Columns(1).Replace "*" & .Range("A" & i).Value & "*", .Range("B" & i).Value, xlPart, , False, , False, False
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top