Worksheet Search – Find Text Coordinates

Searches for a specified text in an Excel.Worksheet and returns its cell coordinates if found.

This function uses Excel’s Find method with case sensitivity enabled and returns the cell position via RowNumber and ColNumber.

    Public Function xlFindTextCoordinates(wsSheet As Excel.Worksheet, 
                                          TextToFind As String,                                           
                                          ByRef RowNumber As Integer, 
                                          ByRef ColNumber As Integer, 
                                          Optional LookAt As Object = xlWhole) As Boolean

        Dim erRange As Excel.Range = wsSheet.Cells.Find(What:=TextToFind, LookIn:=xlValues,
                                        LookAt:=LookAt, SearchOrder:=xlByRows, SearchDirection:=xlNext,
                                        MatchCase:=True, SearchFormat:=False)

        If Not erRange Is Nothing Then
            RowNumber = erRange.Row : ColNumber = erRange.Column
            Return True
        End If

        Return False
    End Function

    Public Function xlFindTextCoordinates(wsSheet As Excel.Worksheet, 
                                          TextToFind As String,                                           
                                          ByRef RowNumber As Integer, 
                                          ByRef ColNumber As Integer, 
                                          Optional LookAt As Object = xlWhole) As Boolean

        Dim erRange As Excel.Range = wsSheet.Cells.Find(What:=TextToFind, LookIn:=xlValues,
                                        LookAt:=LookAt, SearchOrder:=xlByRows, SearchDirection:=xlNext,
                                        MatchCase:=True, SearchFormat:=False)

        If Not erRange Is Nothing Then
            RowNumber = erRange.Row : ColNumber = erRange.Column
            Return True
        End If

        Return False
    End Function

Posted by ipercube