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