Code To Find Info From Sheet 2

[Dazzawm]

I have a list of numbers on sheet 1 starting in A2. What I need the code to do is look for that number that could be anywhere on sheet 2 and put what it is in the adjacent cell to it in column 'B'.

i.e ABC123 may be in A2 on sheet 1 and the sama data may be on sheet 2 in cell J14 and I want it to take what is next to that number in K14 and put it in B2. Thanks.

 

[VaraK]

Vlookup doesn't work?

 

[Dazzawm]

No because like I said it could be anywhere on sheet 2, where I may have 50 columns and 10000 rows for it to look at.

 

[Tom Urtis]

With Sheet1 as the active sheet, see if this macro does what you want:

Sub Test1()
Application.ScreenUpdating = False
Dim cell As Range, varFind As Variant
With Worksheets("Sheet2")
For Each cell In Columns(1).SpecialCells(2)
Set varFind = .Cells.Find(What:=cell.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not varFind Is Nothing Then _
cell.Offset(0, 1).Value = .Cells(varFind.Row, varFind.Column + 1).Value
Set varFind = Nothing
Next cell
End With
Application.ScreenUpdating = True
End Sub

 

[Dazzawm]

Yep, that seemed to work thanks.