DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Yong has posted 5 posts at DZone. View Full User Profile

Excel Vba Find Text In All Cells And Return Row Number

08.12.2011
| 26559 views |
  • submit to reddit
        // 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


    

Comments

Scott Mcscott replied on Tue, 2012/09/25 - 12:43am

This is great, I'm just wondering one thing: can you pass it a page or a range as an argument? What happens if I have two pages in my worksheet with the same value I'm searching for? Would you use sheets.activate if you wanted to search a particular sheet?

Alessandro Del ... replied on Thu, 2011/08/11 - 3:14am

it's so great really