Help On search macro

[acss]

Tom was kind enough to provide this macro but after submitting the first
search and hitting the yes box, the dialog closes even though another search
is required. What can be done so when the user clicks yes the dialog box
stays open for another 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

 

[JW]

Add a goto statement to go to the inputbox. Something like:
asker:
ans = InputBox("Enter flight number: ")
'rest of code here
GoTo asker

Might want to add a check for user input as well so that the macro can
exit. Below the ans=.... line, place something like:
If ans="" Then Exit Sub

 

[Joel]

You don't need a go to statment. Avoid go to unless absolutely necessary.
Here is a better way.


Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
Do
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 with flight " & ans & "?", 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
res = MsgBox("Find new flight?", vbYesNo)
Loop While res = vbYes
End Sub

 

[JW]

You don't need a go to statment. Avoid go to unless absolutely necessary.
Here is a better way.

Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
Do
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 with flight " & ans & "?", 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
res = MsgBox("Find new flight?", vbYesNo)
Loop While res = vbYes
End Sub

Joel, in general, I agree that GoTo is not the best method. However,
for simple things such as this, I have noticed no ill effect from
using them sparingly. I do not recommend using them for tons of
things, as I am sure you have seen code that has 40 million GoTo
statements creating "spaghetti code".

Regards
-Jeff-

 

[Joel]

JW: I have a Masters in Computer Science. Structure Programming is the
standard for programming (avoid goto) because the code is much easier to
understand and maintain. Do you agree that my code is easier to follow than
your code?

 

[JW]

Joel, I wasn't trying to ruffle any feathers. Just providing my $.
02. But, as I said before, I do not recommend using them very often
at all. The only cases I use them for are error handling issues and
nothing else.

That said, after looking back over the OPs request, I concur that a
loop is the proper way to handle this. I was reading it more along
the lines of an error where he needed the user to input something to
continue.

As for a Masters Degree in CS, congrats.

Regards
-Jeff-