PDA

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



wes2706
03-30-2017, 12:13 PM
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
03-30-2017, 01:21 PM
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
03-30-2017, 01:38 PM
Excellent...thank you so much

mancubus
03-31-2017, 02:45 AM
you are welcome.

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