Originally Posted by
gibbo1715
Im really interested in this short function, i am on a mission to shorten the code im my projects at the moment and considered a short search function as below
Function Find(strFind) As Integer
Cells.Find(What:=strFind, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
End Function
Then call it as follows
Sub Test()
Dim SearchFor As String
On Error GoTo Err
SearchFor = Find(InputBox("What do you want to search for", "Search"))
MsgBox ActiveCell.Value
Exit Sub
Err:
MsgBox ("Sorry Not Found")
End Sub
Seems to work OK or am i making a glarring error using this method?
What you are doing is to create a generic find function, so a few suggestions as to how I would do it:
- don't call it Find, not a good idea,
- pass the worksheet as an argument, or at least make it an optional argument
- return the range of the found cell, don't activate it in the function, that is not generic
Function fnFind(strFind, Optional sh) As Range
If IsMissing(sh) Then Set sh = ActiveSheet
On Error Resume Next
Set fnFind = sh.Cells.Find(What:=strFind, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End Function
I would actually add optional, defaulted arguments for all the Find arguments as well.
You would then use it like so
Sub TestfnFind()
Dim SearchFor As Range
Set SearchFor = fnFind(InputBox("What do you want to search for", "Search"))
If SearchFor Is Nothing Then
MsgBox ("Sorry Not Found")
Else
MsgBox SearchFor.Value
End If
End Sub