# Find and replace whole cell by substring

#### Fluff

##### MrExcel MVP, Moderator
You can upload them to a share site such as OneDrive, Google Drive, Dropbox. Mark for sharing & post the link to the thread

### 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
Is the code located in the file with the names you want changing?

#### CaptainCsaba

##### Board Regular
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
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
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
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
You're welcome & thanks for the feedback

#### Fluff

##### MrExcel MVP, Moderator
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
Yeah I noticed, is that because of the XlPart, is there another argument we need to give it?

#### Fluff

##### MrExcel MVP, Moderator
Try the 2nd option from post#9

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...