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..!!

 

[RichardMGreen]

Try this:-

Sub FindUniques ()
Application.ScreenUpdating = False
Dim i as Long
Dim FoundCell as Object
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))  
  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]

Hello RichardMGreen
I tried but Code is not working ...
Thanks

 

[RichardMGreen]

When you say it's not working, are you getting error messages?
Is it doing anything?
What happens if you step through the code?

 

[KOK]

Hi --

I do not get any error messages...but I see that evenif the value could be found in sheet2, the result shows "Value not found"

Thanks in Advance

 

[RichardMGreen]

OK. Replace this line:-
FoundCell = Sheets("Sheet2").Cells.Find(what:=.cells(1,i))

with this one:-
Set FoundCell = Sheets("Sheet2").Cells.Find(what:=.cells(1,i))

 

[KOK]

Thanks a lot, now its working perfect…!!!

 

[KOK]

Hi – if I modify this question as following then will I get the answer ?
Data is in the range “B2:H10” (Sheet1)
Data where to search “C9:S600” (Sheet2)
And the result should be on range “B12:H20” (Sheet1)
Thanks in advance…<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[KOK]

No one knows …. ??

 

[RichardMGreen]

Try this (untested).

Sub FindUniques ()
Application.ScreenUpdating = False
Dim i as Long,rng as Range, FoundCell as Object
for each rng in range("B2:H10")
  On Error Resume Next
  With Sheets("Sheet1")
  Set FoundCell = Sheets("Sheet2").Range("C9:S600").Find(what:=rng.value)  
  If FoundCell Is Nothing Then
    rng.offset(10,10) = "Value not found"
  Else
    rng.offset(10,10) = FoundCell.Address
  End If
Next rng
End With
Application.ScreenUpdating = True
End Sub

 

[KOK]

I faced error on Next statement, It said Compile Error : Next Without For

Thanks