' Input param: Text we want to look for
' Optional input params:
'     Search direction (forward, backward),
'     Search order (in row or column)
' Output: row position of the text being searched

Private Function pFindRowPos(sText As Variant, _
  Optional SearchDirection As XlSearchDirection = xlNext, _
  Optional SearchOrder As XlSearchOrder = xlByRows) As Long

    Dim lResult As Long, oRg As Range

    Set oRg = Cells.Find(What:=sText, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=SearchOrder, _
                 SearchDirection:=SearchDirection, _
    MatchCase:=False, SearchFormat:=False)

    If Not oRg Is Nothing Then lResult = oRg.Row

    pFindRowPos = lResult

    Set oRg = Nothing

End Function

Related Entries

• Finding Cell with Minimum/Maximum Value in Active Worksheet

  Let say we want to find position of cell containing the minimum/maximum value in current/active Excel worksheet, and then after we found the cell, we will change the cell format to make it stand out before other cells. The logic is simple, we just need to use Excel MIN function to find the minimum/maximum value on...

• How To Find the Last Row That Contain Data in Excel?

  UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...

• Remove Non-AlphaNumeric Characters from String

  The function below will allow us to remove a non alphanumeric character from an input string. Be careful though, lengther the string, more time needed to evaluate each string on the sentence. To bad that I don’t have any idea on how to use Regular Expression in Excel VBA, if we can use regex to replace...

• Get Position of Last Column Containing Data with Excel VBA

  If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel. The VBA code is the exact replica of the code to get the last row, only...

• Excel Extract Cell Comments

  Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here). Sub CreateCommentsSummary()Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer,...

Valuable Resources

• Microsoft Excel 2003/2007 Video Tutorials

  Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.

• 101 Secrets of Microsoft Excel

  Discover 101 of Excels little-known secrets that have been hiding right under your nose.

• Top Tips & Tricks to Get You Started in Microsoft Excel 2007

  This is an ebook by Carol Bratt's, a Microsoft Certified Professional who has plenty of experience explaining things in simple English.

• Microsoft Office 2003/XP/2007 Training Videos

  Discover an easy to use, hands-on interactive course that will teach you how to unleash the true power of Microsoft Office 2003/XP/2007.