Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A generic "find" function
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A generic "find" function
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A generic "find" function
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "xp" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A generic "find" function
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] On Wed, 5 May 2010 05:44:01 -0700, xp wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Whats wrong with this? MyWBAccRep.Sheets("Volumes").Cells.Find("latest").Copy.Offset(0, | Excel Programming | |||
use a macro to "Save As" to a generic desktop | Excel Programming | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Is there a "generic" Error Handling method | Excel Programming |