Find and replace whole cell by substring
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Find and replace whole cell by substring

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

    Default Re: Find and replace whole cell by substring

    You can upload them to a share site such as OneDrive, Google Drive, Dropbox. Mark for sharing & post the link to the thread
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Find and replace whole cell by substring

    Is the code located in the file with the names you want changing?
    - 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. #13
    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 think not, it is saved separately but it is here in the forum post if you need it. Sorry about me forgetting to include it.

  4. #14
    Board Regular
    Join Date
    Nov 2013
    Posts
    623
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and replace whole cell by substring

    Try this code

    Code:
    Sub FindAndReplaceing()
        Dim NameListWB As Workbook, thisWb As Workbook
        Dim NameListWS As Worksheet, thisWs As Worksheet
        Dim i As Long, lRow As Long
        Dim Frng As Range
    
    
        '~~> 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
                Set Frng = .Columns(1).Find(thisWs.Range("A" & i).Value)
                
                If Not Frng Is Nothing Then
                thisWs.Columns(1).Range("A" & i).Value = Frng.Offset(0, 1).Value
                End If
            Next i
        End With
    End Sub
    Last edited by kvsrinivasamurthy; Jul 4th, 2019 at 09:17 AM.

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

    Default Re: Find and replace whole cell by substring

    If the code is not in the same workbook as the values to be changed, then change this line
    Code:
    Set thisWb = ThisWorkbook
    to
    Code:
    Set thisWb = ActiveWorkbook
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    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

    It was this! It finally works! Thank you so much Fluff, this will save so much work! I'll post the final code here for anybody who find this later:

    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 = ActiveWorkbook
        '~~> 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 .Range("A" & i).Value, .Range("B" & i).Value, xlPart, , False, , False, False
            Next i
        End With
    End Sub

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

    Default Re: Find and replace whole cell by substring

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Find and replace whole cell by substring

    One thing, judging by the strings you showed in post#5 I would expect you to end up with
    BREWIN DOLPHIN HOLDINGS PLC PLC
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    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

    Yeah I noticed, is that because of the XlPart, is there another argument we need to give it?

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

    Default Re: Find and replace whole cell by substring

    Try the 2nd option from post#9
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •