Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help On search macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help On search macro
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 acss wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help On search macro
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" wrote: 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 acss wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help On search macro
On Oct 13, 2:01 am, Joel wrote:
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" wrote: 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 acss wrote: 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 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- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help On search macro
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" wrote: On Oct 13, 2:01 am, Joel wrote: 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" wrote: 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 acss wrote: 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 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- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help On search macro
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- Joel wrote: 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" wrote: On Oct 13, 2:01 am, Joel wrote: 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" wrote: 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 acss wrote: 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 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro search | Excel Discussion (Misc queries) | |||
A Macro to search and sum | Excel Programming | |||
Search macro | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |