Find Data From Sheet2

[Dazzawm]

I have a list of numbers on sheet 1 in column A. What I need to do is look for those numbers on sheet 2 and put what is in the adjacent cell on sheet 2 into column B on sheet 1. Thanks.

 

[Arcticwarrio]

look in your formulas for VLOOKUP that will do what you want

Arctic.

 

[Dazzawm]

look in your formulas for VLOOKUP that will do what you want

Arctic.

VLOOKUP won't work because the data I need to find could be anywhere on sheet 2, 30 columns wide by 20000 rows.

 

[Arcticwarrio]

you will need something like:

in B1 put

=VLOOKUP(A1,'Sheet 2'!A1:B500,1,FALSE)

and drag it down

asuming the numbers in sheet 2 are in colum A and the data is in column B

 

[Arcticwarrio]

in that case you should be able to adapt this code to look for it
im in work at the mo and dont have time sorry,

Sub F_Search()
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
PartNumber = InputBox("Enter Delivery Number", "Del Note Number", "")
If PartNumber = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=PartNumber, LookIn:=xlFormulas, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .name
AddressStr = AddressStr & .name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address
Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select
Set Found = .UsedRange.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
Next ws
If Len(AddressStr) Then
Else:
MsgBox "Unable to find " & PartNumber & " in this workbook.", vbExclamation
End If
Exit Sub
End Sub

 

[Dazzawm]

in that case you should be able to adapt this code to look for it
im in work at the mo and dont have time sorry,

Thanks but I don't know enough about VBA to adapt codes. I will need a code done for me by someone if poss. Thanks.

 

[Arcticwarrio]

just thought you may be able to use index and or match functions instead

http://www.contextures.com/xlFunctions03.html

Arctic.

 

[Dazzawm]

I thought formulas/functions would only be useful if they all came from the same column. As I said I dont know where they will be on sheet 2 among the possible 100,000+ cells.

 

[Arcticwarrio]

i dont have 2007 here but i think theres a search function in there too