skaisdead22
New Member
- Joined
- Dec 17, 2020
- Messages
- 15
- Office Version
- 2013
- Platform
- Windows
Hello,
I am trying to utilize VBA to perform an absolute find and replace on a column of data within my workbook. It is essentially a glorified vlookup but replaces the data instead.
Presently, my code looks something like this:
Sub ReplaceNoS()
Dim myRange As Range, myList As Range
Set myRange = Sheets("Sheet1").Columns("E:E")
Set myList = Sheets("Sheet2").Columns("B:C")
For Each cel In myList.Columns(1).Cells
myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
Next
End Sub
Sheet1 represents the sheet containing the old values that need to be replaced and Sheet2 contains the paired columns where the old value can be matched with its target data present directly in the column to its right.
The issue I am having is the current code can find a partial string and replace its corresponding value before it finds the full absolute match. Example. if in my Sheet1 there is a value "hot" and in Sheet2 the value "hot" is directly next to "123" the code will perform that replacement. However, in Sheet1 there is a value "hotpocket" and in Sheet2 the value "hotpocket" is directly next "456" the code would produce "123pocket" instead of the desired "456".
Essentially I am just asking if anyone has created a absolute find and replace script where the find is a list of values and the replace is from a list of paired values that are side by side in separate columns.
Thanks,
skaisdead22
I am trying to utilize VBA to perform an absolute find and replace on a column of data within my workbook. It is essentially a glorified vlookup but replaces the data instead.
Presently, my code looks something like this:
Sub ReplaceNoS()
Dim myRange As Range, myList As Range
Set myRange = Sheets("Sheet1").Columns("E:E")
Set myList = Sheets("Sheet2").Columns("B:C")
For Each cel In myList.Columns(1).Cells
myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
Next
End Sub
Sheet1 represents the sheet containing the old values that need to be replaced and Sheet2 contains the paired columns where the old value can be matched with its target data present directly in the column to its right.
The issue I am having is the current code can find a partial string and replace its corresponding value before it finds the full absolute match. Example. if in my Sheet1 there is a value "hot" and in Sheet2 the value "hot" is directly next to "123" the code will perform that replacement. However, in Sheet1 there is a value "hotpocket" and in Sheet2 the value "hotpocket" is directly next "456" the code would produce "123pocket" instead of the desired "456".
Essentially I am just asking if anyone has created a absolute find and replace script where the find is a list of values and the replace is from a list of paired values that are side by side in separate columns.
Thanks,
skaisdead22
