VBA to replace text of cell in one tab with text of cell in another tab (with condition)

[LoverBoA]

Hi all, looking to write a code to make my work simpler. Basically there are two tabs I work with, one is the master tab ("Master" tab) and the other tab ("Updated" tab) with updated data each month.

Hope anyone can advise on the code to fulfil the below purpose:-

1. Find, in column A (starting from cell A2 to the last cell A that is Non-Blank) of the "Updated" tab, using the text in cell B2 of the "Master" tab;
2. If the Find is successful at row X of the column A ("Updated" tab), replace cells D2, E2, F2 of the "Master" tab with values of the matched row X (in "Updated" tab), cells KX, LX, MX respectively;
3. If the Find is not successful, skip error.
4. Repeat step 1 for next cell B3 of the "Master" tab;
5. End process when the macro reaches the last cell of column B having text i.e. the next cell B is blank.

The macro needs to run fast for a few thousand iterations hence would appreciate any advice on improving my above logic as well!

Thanks for any advice!

 

[RudiS]

Try this code...

Sub FindAndUpdate()
Dim shM As Worksheet
Dim shU As Worksheet
Dim rData As Range
Dim rC As Range
Dim rF As Range
    Set shM = Sheets("Master")
    Set shU = Sheets("Updated")
    Set rData = shM.Range(shM.Cells(2, "B"), shM.Cells(Rows.Count, "B").End(xlUp))
    Application.ScreenUpdating = False
    For Each rC In rData.Cells
        Set rF = shU.Columns("A:A").Find(What:=rC.Value, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not rF Is Nothing Then
            rC.Offset(0, 2).Value = rF.Offset(0, 10).Value
            rC.Offset(0, 3).Value = rF.Offset(0, 11).Value
            rC.Offset(0, 4).Value = rF.Offset(0, 12).Value
        End If
    Next rC
    Application.ScreenUpdating = True
    MsgBox "Data processing completed.", vbInformation
End Sub

 

[LoverBoA]

Thanks, RudiS! Code works like a gem!