Find and replace whole cell by substring

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,327
Office Version
365
Platform
Windows
You can upload them to a share site such as OneDrive, Google Drive, Dropbox. Mark for sharing & post the link to the thread
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,327
Office Version
365
Platform
Windows
Is the code located in the file with the names you want changing?
 

CaptainCsaba

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

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
628
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:

Fluff

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

CaptainCsaba

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,327
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,327
Office Version
365
Platform
Windows
One thing, judging by the strings you showed in post#5 I would expect you to end up with
BREWIN DOLPHIN HOLDINGS PLC PLC
 

CaptainCsaba

Board Regular
Joined
Dec 8, 2017
Messages
78
Yeah I noticed, is that because of the XlPart, is there another argument we need to give it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,327
Office Version
365
Platform
Windows
Try the 2nd option from post#9
 

Watch MrExcel Video

Forum statistics

Threads
1,101,754
Messages
5,482,686
Members
407,358
Latest member
Maze123

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