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

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

| 17999 views |

        // 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

Tags:

Excel
VBA

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?