HELP me PLZ

[KOK]

Hello All,
I require help for the following question :
I have data in Sheet1, Row 1,which contains unique data , and this data
scattered in Sheet2, which is also unique.
Now, on Sheet1 , on Row2, I want the address of cell where the cell can
be found in Sheet2.
For eg. On Sheet1 Cell A1 - contains value - 100, and it can be found on
Sheet2 at c55, now I want this cell reference on Sheet1, B1.

Thanks in Advance..!!

 

[Ron99]

Hi...

Are there any duplicate numbers in the lookup column ?

 

[SteveO59L]

Anywhere on sheet 2, or is your data within a specific range?

Pre Excel2007 a sheet consisted of 256 columns x 65, 000 rows, so more than 16 million cells in which to look.

 

[JackDanIce]

Try:

Sub FindUniques ()
Application.ScreenUpdating = False
Dim i as Long
Dim FoundCell as Range
On Error Resume Next
With Sheets("Sheet1")
For i = 1 to .cells(1, Columns.Count).End(xlToLeft).Column
  FoundCell = Sheets("Sheet2").Cells.Find(what:=.cells(1,i), after:=Sheets("Sheet2").cells(1,1).Address
  If FoundCell Is Nothing Then
    .cells(2,i) = "Value not found"
  Else
    .cells(2,i) = FoundCell.Address
  End If
Next i
End Sub

 

[KOK]

Data is unique in lookup and in sheet2 data is anywhere...

Hello JackDanIce

When I ran the code I found error for the line
FoundCell = Sheets("Sheet2").Cells.Find(what:=.cells(1,i), after:=Sheets("Sheet2").cells(1,1).Address

Thanks..

You guys are really HELPFULL>>!!

 

[JackDanIce]

My error, try:

Sub FindUniques ()
Application.ScreenUpdating = False
Dim i as Long
Dim FoundCell as Range
On Error Resume Next
With Sheets("Sheet1")
For i = 1 to .cells(1, Columns.Count).End(xlToLeft).Column
  FoundCell = Sheets("Sheet2").Cells.Find(what:=.cells(1,i), after:=Sheets("Sheet2").cells(1,1))  
  If FoundCell Is Nothing Then
    .cells(2,i) = "Value not found"
  Else
    .cells(2,i) = FoundCell.Address
  End If
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[KOK]

Now I get error for END SUB,,,,

 

[KOK]

Ok I got it now , I made correction with : End With

thank you very much all of you ....

 

[KOK]

Sorry Guys , the code is not working…

 

[KOK]

Is it possible if fix the range on sheet2 say for eg A1 to A10 ?