Find and replace whole cell by substring
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Find and replace whole cell by substring

  1. #1
    Board Regular
    Join Date
    Dec 2017
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find and replace whole cell by substring

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Find and replace whole cell by substring

    Try
    Code:
                thisWs.Columns(1).replace What:=.Range("A" & i).Value, _
                                          Replacement:=.Range("B" & i).Value, _
                                          LookAt:=xlPart _
                                          SearchOrder:=xlByColumns, _
                                          MatchCase:=False
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Dec 2017
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace whole cell by substring

    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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Find and replace whole cell by substring

    Do any of the values in col A of sheet1 in the namechanges workbook exist in the other sheet?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2017
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace whole cell by substring

    Quote Originally Posted by Fluff View Post
    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Find and replace whole cell by substring

    Check for leading/trailing spaces & also check that you don't have any special/hidden characters
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Dec 2017
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace whole cell by substring

    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.

  8. #8
    Board Regular
    Join Date
    Dec 2017
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace whole cell by substring

    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?

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Find and replace whole cell by substring

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Dec 2017
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace whole cell by substring

    Still nothing unfortunately. There must be something I am not noticing. Can I send you the two files somehow?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •