A
acss
I have the following code for a macro which works but does not provide and
additional seach after hitting the yes on the dialog box so instead it closes
rather than give the user the option to enter the next search:
Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
ans = InputBox("Enter flight number: ")
If Len(Trim(ans)) = 0 Then Exit Sub
For Each sh In Worksheets
Set rng = _
sh.Columns(8).Find(What:=ans, _
After:=sh.Range("H65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
saddr = rng.Address
Do
Application.Goto rng, True
res = MsgBox("Continue Searching?", vbYesNo)
If res = vbNo Then Exit Do
Set rng = sh.Columns(8).FindNext(rng)
Loop While rng.Address <> saddr
End If
If res = vbNo Then Exit For
Next
End Sub
what am i missing here so when a user clicks yes it offers another search?
additional seach after hitting the yes on the dialog box so instead it closes
rather than give the user the option to enter the next search:
Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
ans = InputBox("Enter flight number: ")
If Len(Trim(ans)) = 0 Then Exit Sub
For Each sh In Worksheets
Set rng = _
sh.Columns(8).Find(What:=ans, _
After:=sh.Range("H65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
saddr = rng.Address
Do
Application.Goto rng, True
res = MsgBox("Continue Searching?", vbYesNo)
If res = vbNo Then Exit Do
Set rng = sh.Columns(8).FindNext(rng)
Loop While rng.Address <> saddr
End If
If res = vbNo Then Exit For
Next
End Sub
what am i missing here so when a user clicks yes it offers another search?