I have the code below to search across multiple sheets of an active workbook and I have modified it to search inside a cell instead of searching for a complete cell value which helps find partial matches.
I need this find function to move to the next instance of a match until all sheets have been searched.
I would prefer this to function as the Find function inside Excel where you can click Next to take you to the next instance but this code stops when it finds the first match. I would like the option to click Next to move to the next one or click Exit or something to stop it at the current selection that it has found, once again similar to the Find function in Excel.
I believe a .FindNext function would work but figure out how to incorporate it into the code.
Any ideas ?
thanks !
[VBA]
Sub FindData()
Dim MyData As String
Dim wks As Worksheet
Dim rngFoundData As Range
Dim firstaddress As String
' get users data
MyData = InputBox("Please enter the value to search for.")
If MyData = "" Then Exit Sub
' search all sheets in workbook
For Each wks In Worksheets
' find data in current worksheet
'' LookAt:=xlWhole
Set rngFoundData = wks.Cells.Find(What:=MyData, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
' select found data and exit sub
If Not rngFoundData Is Nothing Then
wks.Activate
rngFoundData.Select
Exit Sub
End If
Next wks
' tell user data wasn't found
If rngFoundData Is Nothing Then
MsgBox MyData & " was not found in " & ActiveWorkbook.Name, vbInformation
End If
End Sub
[/VBA]
Reply With Quote
Originally Posted by bdsii
