What's wrong with my UDF?

[Ardmore7]

My user-defined formula is supposed to find the address of a cell that contains "TextToLookFor" in worksheet "Sheet"? Can you telll me what is wrong with it?


Function FindIt(TextToLookFor, Sheet) As String

Dim myCell As Range

myCell = Cells.Find(What:=TextToLookFor, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, MatchCase:=False, SearchFormat:=False).Activate
FindIt = myCell.Address(False, False)

End Function

 

[Richard Schollar]

Syntactically your use of Find is incorrect (you would need to use a Set statement), also you can't use a UDF to actually activate any other part of the sheet in which it resides. Additionally, you haven't specified that sheet Sheet is to be searched. Try the following:

Function FindIt(ByVal TextToLookFor As String, ByVal strSheet As String) As String
    
    Dim myCell As Range

With Sheets(strSheet)
    
    Set myCell = .Cells.Find(What:=TextToLookFor, LookIn:=xlFormulas, _
        LookAt:=xlPart)

End With

If Not myCell Is Nothing then
       FindIt = myCell.Address(False, False)
Else
  FindIt = "Not Found!"
End If
    
End Function

You will also need to be using at least xl2002 for this to work I believe.

EDIT: use in your sheet like this:

=FINDIT("SomeText","YourSheetNameHere")

Richard

 

[Ardmore7]

Richard,

Wow! you're the best.

Thank you. It works great.

Tom

 

[garggaurav]

Thanks RicharSchollar


it is of gr8 help to me