find and replace unique values on another sheet

[pann8888]

Hello! I have a spreadsheet that others will use to correct data entries for a unique identifier (i.e., there is only one instance of that text string on a given sheet.) In short, the main spreadsheet imports data from different spreadsheets, and any incorrect unique identifiers must be corrected in the main spreadsheet before other operations can be performed.

I am looking for a VBA-based way to find the Unique Identifier entry from column B, Sheet 1 in Column B, Sheet 8, and replace it with the value in the same row on Sheet 1, Column 6. (Sheet 8 contains sensitive data and is hidden from users). A sample layout of Sheet 1 is below. In my example, ABC123 would be replaced in Sheet 8 Column B with ADB123 (and that would happen with every other unique identifier on Sheet 1, CDE456 being replace in Sheet 8 by CDF457 and so on). As stated above, there is never more than one instance of the unique identifier on either Sheet 1 or Sheet 8. I have used my Google-fu on various posted VBA solutions, but nothing has been close enough to allow my novice-level coding skills to parse a solution. Can someone point me in the right direction, or even suggest a combination of VBA functions to make this happen? Thanks!

[TABLE="width: 582"]
<tbody>[TR]
[TD]Unique Identifier
[/TD]
[TD]Name
[/TD]
[TD]Email
[/TD]
[TD]mismatch?
[/TD]
[TD][/TD]
[TD]Enter the corrected Unique Identifier below
[/TD]
[/TR]
[TR]
[TD]ABC123
[/TD]
[TD]abc
[/TD]
[TD]abc@abc.com
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD]ADB123
[/TD]
[/TR]
[TR]
[TD]CDE456
[/TD]
[TD]abcd
[/TD]
[TD]abc@abc.com
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD]CDF457
[/TD]
[/TR]
[TR]
[TD]FGH678
[/TD]
[TD]abcde
[/TD]
[TD]abc@abc.com
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD]FGH677
[/TD]
[/TR]
[TR]
[TD]IJK9111
[/TD]
[TD]abcded
[/TD]
[TD]abc@abc.com
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD]IJL1111
[/TD]
[/TR]
</tbody>[/TABLE]

 

[pann8888]

I figured this out, thanks to a 2016 post by Joe4.

Sub myReplace()
Dim myDataSheet As Worksheet
Dim myReplaceSheet As Worksheet
Dim myLastRow As Long
Dim myRow As Long
Dim myFind As String
Dim myReplace As String

' Specify name of Data sheet
Set myDataSheet = Sheets("whatever")

' Specify name of Sheet with list of replacements
Set myReplaceSheet = Sheets("whatever2")

myLastRow = 141

Application.ScreenUpdating = False

' Loop through all list of replacements
For myRow = 22 To myLastRow

' Get find and replace values (from columns A and B)
myFind = myReplaceSheet.Cells(myRow, "B")
myReplace = myReplaceSheet.Cells(myRow, "G")
' Start at top of data sheet and do replacements
myDataSheet.Activate
Range("B5").Select
' Ignore errors that result from finding no matches
On Error Resume Next
' Do all replacements on column B of data sheet
If myReplace <> "" Then
Columns("B:B").Replace What:=myFind, Replacement:=myReplace, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' Reset error checking
On Error GoTo 0
End If
Next myRow

Application.ScreenUpdating = True

MsgBox "Replacements complete!"

End Sub