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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,369
Office Version
365
Platform
Windows
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
 

CaptainCsaba

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,369
Office Version
365
Platform
Windows
Do any of the values in col A of sheet1 in the namechanges workbook exist in the other sheet?
 

CaptainCsaba

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,369
Office Version
365
Platform
Windows
Check for leading/trailing spaces & also check that you don't have any special/hidden characters
 

CaptainCsaba

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

CaptainCsaba

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,369
Office Version
365
Platform
Windows
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
 

CaptainCsaba

Board Regular
Joined
Dec 8, 2017
Messages
78
Still nothing unfortunately. There must be something I am not noticing. Can I send you the two files somehow?
 

Watch MrExcel Video

Forum statistics

Threads
1,101,815
Messages
5,483,075
Members
407,377
Latest member
JennaWashburn

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top