Gaura215 wrote on 31-07-12 :
Hello

I was trying to google it and checked on forums, but couldnt
successfully found any solution to my problem.

I have a list of names in coloum D of a worksheet called "Trial", I have
another worksheets called "Usage" with this list. I need a macro which
can search the name written in Trial worksheet in Usage and worksheet,
and activate the found results in usage worksheet.

I cannot use vlookup formulae here as "trial" sheet only has last names,
which "usage" sheet has full names.

Please help me, this is an urgent report which I need to make asap.

Regards
Gaurav

=================================
Public Sub Trial_Usage()
Dim RangeArr(1 To 2) As Range, CellFound As Range
Dim i As Long, NameToFind As String

' Definition -------------------------
Set RangeArr(1) = [Trial!D1]
Set RangeArr(2) = [Usage!D1]
NameToFind = InputBox("Name to find")
' ------------------------------------

For i = 1 To 2
Set RangeArr(1) = RangeArr(1).Resize(RangeArr(1).End(xlDown).Row)
Set RangeArr(2) = RangeArr(2).Resize(RangeArr(2).End(xlDown).Row)
Next

With RangeArr(1)
Set CellFound = .Find(What:=NameToFind, _
MatchCase:=False, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
LookAt:=xlWhole, _
LookIn:=xlValues)
If CellFound Is Nothing Then
MsgBox NameToFind & " not found in " &
RangeArr(1).Worksheet.Name
Exit Sub
End If
End With

With RangeArr(2)
Set CellFound = .Find(What:=NameToFind, _
MatchCase:=False, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
LookAt:=xlPart, _
LookIn:=xlValues)
If CellFound Is Nothing Then
MsgBox NameToFind & " not found in " &
RangeArr(2).Worksheet.Name
Else
RangeArr(2).Worksheet.Activate
CellFound.Select
End If
End With

End Sub
=======================================

Let me know if it works.
Bruno