How to Find the data in another worksheet and place replace the corresponding value using excel VBA

Question

I have two worksheets, in the second worksheet i have defined all the parameters unique name

in the first sheet i have defined the just the parameters name, on the click of Auto Fill Button i want it to go and check all the parameters name in the Acronym sheet and if the match is found it should replace the particular parameter's unique id.

Could anyone please tell me how to achieve this using excel VBA, Any help is appreciated!

Answer 1

Loop through one to find the other.

    Sub DoIt()
    Dim sh As Worksheet, ws As Worksheet
    Dim LstRw As Long, rng As Range, c As Range, Frng As Range
    Set sh = Sheets("Sheet1")    'Acronym Sheet
    Set ws = Sheets("Sheet2")

    With sh
        LstRw = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set rng = .Range("B2:B" & LstRw)
    End With

    With ws
        For Each c In rng.Cells
            Set Frng = .Range("C:C").Find(what:=c, lookat:=xlWhole)
            If Not Frng Is Nothing Then
                Frng.Value = c.Offset(, -1).Value
            Else:
            End If
        Next c
    End With

End Sub