A generic "find" function

[xp]

Using XL2007.

I need a reusable "generic" function that I can call and feed it three
variables:

1) a string to find
2) the column to find it in
3) whether to return the cell address of the first occurrence (top) or last
(bottom)

Can someone please help me out with this?

Thanks!

 

[SteAXA]

This function find the first cell (from top or from bottom), return the name
of cell and stop.
If you need to go to the next or preview cell you must change the function.

Function ToSearchFor(ByVal sToFind As String, ByVal sColumn As String, ByVal
sFrom As String)
If sToFind = "" And sColumn = "" And Not (sFrom = "T" Or sFrom = "B") Then
Exit Function
End If

Dim nRow As Integer
Dim bStop As Boolean
Dim sTest As String

Range(sColumn & "1").Select
If sFrom = "T" Then
nRow = 1
Else
Selection.End(xlDown).Select
nRow = ActiveCell.Row
End If
While Not bStop
sTest = ActiveCell.Value
If sTest = "" Then
MsgBox "Text not finded in column " & sColumn
bStop = True
Else
If InStr(1, sTest, sToFind, vbTextCompare) > 0 Then
MsgBox "Text finded in cell " & sColumn & nRow
bStop = True
End If
End If
If Not bStop Then
If sFrom = "T" Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(-1, 0).Select
End If
End If
If sFrom = "T" Then
nRow = nRow + 1
Else
nRow = nRow - 1
End If
Wend

End Function

You can call the function draw a userform or like this:

Sub SearchFor()
Dim sToFind As String
Dim sColumn As String
Dim sFrom As String

sToFind = InputBox("String to find")
sColumn = InputBox("Column to search in")
sFrom = InputBox("For start to top type T, to bottom B, E for exit
function")

ToSearchFor sToFind, sColumn, sFrom

End Sub

Good work!

 

[Don Guillett]

Try this function in a REGULAR module using the formula
=fmv("a","p","f")
where a is the string
p is the column and f is for first or l for last


Option Explicit
Function fmv(mv As String, mc As String, fl As String)
Dim mcl As Long
mcl = Cells(1, mc).Column
If UCase(fl) = "F" Then
fmv = Columns(mcl).Find(What:=mv, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Address
Else
fmv = Columns(mcl).Find(What:=mv, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False).Address
End If
End Function

 

[Chip Pearson]

I have a generic find proc at
http://www.cpearson.com/excel/FindAll.aspx that will return a Range
object containing the cells in which the searched-for text was found.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]