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

  • 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



Scott Mcscott replied on Mon, 2012/09/24 - 11:43pm

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 - 2:14am

it's so great really