Read text and find the corresponding value in another worksheet

[niketmohan]

Hi,

I am new to VBA and need a macro to do the following for me

1. Read the text in Sheet 1 - Column A.
2. Find that text in Sheet 2 -Column B
3. If found, copy the corresponding value in Column A of Sheet 2 and paste it in Column B of sheet 1.

4. If not found, leave the column blank.

5. Repeat the above for each row in column A of Sheet1.

I have attached a sample worksheet.

In brief, I want the macro to read each text in every row of column A(Sheet1) and search it in column B of sheet 2. If found, copy the corresponding value in Column A of sheet2 to corresponding column B of sheet one of the text being searched.

Thanks for helping me out

[MaczaQ]

try this:

updated example in attachment

Private Sub CommandButton1_Click()
Dim r As Integer
Dim from, dest As Object

Set dest = Sheets("Sheet1")
Set from = Sheets("Sheet2")

For r = 1 To dest.UsedRange.Rows.Count
  On Error Resume Next
  dest.Cells(r, 2).Value = from.Range("B:B").Find(dest.Cells(r, 1).Value, SearchOrder:=xlByRows).Offset(, -1).Value
Next r
End Sub

Best Regards
MaczaQ

[niketmohan]

Thanks Mac .... Works like a charm