Search for a word and get the (row, column) numbers, when the word is found.

[wes2706]

Friends, I need to use VBA to search for a word in an Excel worksheet and get the (row, column) numbers, when the word is first found. Can someone please help?

[mancubus]

welcome to the forum.

Sub vbax_59048_return_first_match_row_column_numbers_of_search_string()
    
    Dim SearchString As String
    Dim FoundCell As Range
    
    SearchString = "string to find here"
    
    With Sheets("Sheet1").UsedRange 'change Sheet1 to suit
        Set FoundCell = .Cells.Find(What:=SearchString, _
            After:=.Cells(.Cells.Count), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)

        If Not FoundCell Is Nothing Then
            MsgBox "Row Number: " & FoundCell.Row & vbCr & "Column Number: " & FoundCell.Column
        Else
            MsgBox "Search String not found"
        End If
    End With

End Sub

[wes2706]

Excellent...thank you so much

[mancubus]

you are welcome.

pls mark the thread as solved from Thread Tools for future references.