Find String and Replace With Adjacent Cell

Hello,

I am trying to do the following. I have two sheets. In sheet one a have a column of data (A). In sheet two I have two columns of data (B and C). I want to match each cell in A to B, and then return the adjacent value in C. Can anyone help me do this? I have been struggling with the Find and Replace VBA function. Thanks.

Steve

Steve,

Give this a try:

Sub tgr()
    
    Dim rngReplace As Range
    Dim rngFound As Range
    Dim arrReplace As Variant
    Dim ReplaceIndex As Long
    
    Set rngReplace = Sheets(1).Range("A1", Sheets(1).Cells(Rows.Count, "A").End(xlUp))
    arrReplace = rngReplace.Value2
    
    With Sheets(2).Columns("B")
        For ReplaceIndex = 1 To UBound(arrReplace, 1)
            .NumberFormat = "General"
            Set rngFound = .Find(arrReplace(ReplaceIndex, 1), .Cells(.Cells.Count), xlValues, xlWhole)
            If Not rngFound Is Nothing Then
                arrReplace(ReplaceIndex, 1) = rngFound.Offset(, 1).Text
                Set rngFound = Nothing
            End If
        Next ReplaceIndex
    End With
    
    rngReplace.Value = arrReplace
    
    Erase arrReplace
    Set rngReplace = Nothing
    
End Sub

Tiger this works great! Thanks for the quick and helpful response. I look forward to figuring out exactly how it works.

Steve

You're very welcome