[VBA] Find & Replace with Loop for Named Range

[Seba Robles]

Hello,

I'm not very familiarized with loops and would like your help.

I'm trying to create a code where I will Find text from a named range and Replace it from a List

Sheet 1 has myRange named
Sheet 2 has a List; Column A myFind and Column B myReplace

myFind is the text I want to find from the myRange in Sheet 1
myReplace is the text I want to replace it with
​

I want to create a code that finds the text of myRange in Sheet 1 and replaces it with the replacement of the list in column B Sheet 1.

The code should go through every row of the list in Sheet 2 and replacing it in myRange of Sheet 1 only.

Can you guys please help me out? Thanks in advance!

 

[DanteAmor]

Try this

Sub Find_Replace()
    Dim sh1 As Worksheet, sh2 As Worksheet, myFind As Range
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    For Each myFind In sh2.Range("A1", sh2.Range("A" & Rows.Count).End(xlUp))
        sh1.Range("myRange").Replace What:=myFind, Replacement:=myFind.Offset(0, 1), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next
End Sub

 

[JLGWhiz]

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        sh1.Range("myRange").Replace c.Value, c.Offset(, 1).Value
    Next
End Sub