Hello -
I retrieved this code from another message board (not sure who the author is, or I would give that person credit) and I need assistance with it.
When a search only finds one occurrence and the user clicks "continue" to keep searching, the code takes the user to the end of the sheets and returns a "not found" message. This makes the user start the search all over again to get to the one occurrence and be smart enough to say "no" when asked if they want to continue or not. Ideally, if there are no other occurrences found, it would keep the single occurrence selected and allow the user to stay at that location, rather than taking the user to the end of the worksheets, thereby losing their search result.
Can anyone assist me with this?
~~~~~~~~~~~~~~~~~~~~~~~~
Sub FindAcrossAll()
Dim DoIt As Boolean
Dim What As String
DoIt = True
While DoIt
What = InputBox("What are you looking for?")
For Each Sht In Worksheets
Sht.Activate
Set Found = Sht.UsedRange.Find(What)
If Not Found Is Nothing Then ' The value has been found.
FirstAddress = Found.Address
Do
Found.Activate
Msg = "Continue the search ?"
Title = "Continue ?"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)
If Response = vbNo Then ' Doesn't want to continue
MsgBox "Search cancelled by user."
Found.Activate
Exit Sub ' Quit the macro
End If
Set Found = Cells.FindNext(After:=ActiveCell)
If Found.Address = FirstAddress Then Exit Do
Loop
End If
Next Sht
If Found Is Nothing Then ' Nothing found
Msg = "Not found! Do you want to start a new search?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Else
Msg = "Search complete. Do you want to start a new search?"
Style = vbYesNo + vbDefaultButton2
Found.Activate
End If
Title = "Search Complete"
Response = MsgBox(Msg, Style, Title)
If Response <> vbYes Then DoIt = False
Wend
MsgBox ("Search has ended.")
End Sub
I retrieved this code from another message board (not sure who the author is, or I would give that person credit) and I need assistance with it.
When a search only finds one occurrence and the user clicks "continue" to keep searching, the code takes the user to the end of the sheets and returns a "not found" message. This makes the user start the search all over again to get to the one occurrence and be smart enough to say "no" when asked if they want to continue or not. Ideally, if there are no other occurrences found, it would keep the single occurrence selected and allow the user to stay at that location, rather than taking the user to the end of the worksheets, thereby losing their search result.
Can anyone assist me with this?
~~~~~~~~~~~~~~~~~~~~~~~~
Sub FindAcrossAll()
Dim DoIt As Boolean
Dim What As String
DoIt = True
While DoIt
What = InputBox("What are you looking for?")
For Each Sht In Worksheets
Sht.Activate
Set Found = Sht.UsedRange.Find(What)
If Not Found Is Nothing Then ' The value has been found.
FirstAddress = Found.Address
Do
Found.Activate
Msg = "Continue the search ?"
Title = "Continue ?"
Response = MsgBox(Msg, vbYesNo + vbQuestion, Title)
If Response = vbNo Then ' Doesn't want to continue
MsgBox "Search cancelled by user."
Found.Activate
Exit Sub ' Quit the macro
End If
Set Found = Cells.FindNext(After:=ActiveCell)
If Found.Address = FirstAddress Then Exit Do
Loop
End If
Next Sht
If Found Is Nothing Then ' Nothing found
Msg = "Not found! Do you want to start a new search?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Else
Msg = "Search complete. Do you want to start a new search?"
Style = vbYesNo + vbDefaultButton2
Found.Activate
End If
Title = "Search Complete"
Response = MsgBox(Msg, Style, Title)
If Response <> vbYes Then DoIt = False
Wend
MsgBox ("Search has ended.")
End Sub
This is exactly what I needed! Thank you very much to each of you that worked on this problem...I appreciate it!!!
