Finding a Value in the Worksheet

This is the code to find a value in a worksheet or selected group of cells. This code can only highlight the value when found.

Dim strValueToFind As String
strValue = “excel vba”
Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Activate

To store the row location of the found item, use the following syntax:

Dim intRow As Integer
Dim strValueToFind As String
strValue = “excel vba”
intRow = 0
‘You need to zero out the integer variable first so that when the item is not found it remains zero
intRow = Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Row
If intRow = 0 then

MsgBox “Item Not Found”

Else

MsgBox “Item is located at Row: “ & intRow

End if

For the column location:

Dim intColumn As Integer
Dim strValueToFind As String
strValue = “excel vba”
intColumn = 0
‘You need to zero out the integer variable first so that when the item is not found it remains zero
intColumn = Selection.Find(What:=strValue, After:=ActiveCell, _
Lookin:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, matchCase:=False, _
SearchFormat:=False).Column
If intColumn = 0 then

MsgBox “Item Not Found”

Else

MsgBox “Item is located at Column: “ & intColumn

End if

Click here to subscribe and receive Pro-business Excel VBA Programming tips.

If you like this post in Pro-business Excel VBA Programming, buy me a coffee.