Excel vba find text in all cells and return row number

Friday, 12 August 2011 00:31

// description of your code here
Same as Control+F



Public Function FindRowPos(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

 

    FindRowPos = lResult

 

    Set oRg = Nothing

End Function