Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
I have a workbook that that is sent to me on a monthly basis and it contains
8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
You could group all the sheet and to Edit=Find
if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
Thanks Tom,
I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
No, the macro can be placed in the personal.xls file and executed on the
active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
I work in a multi-user enviorment and the monthly workbook sent to me would
be dropped into a network folder. I need to know if by using this macro, will it affect other users excell usage from their desktop or only this workbook in the network folder when they open it? Sorry for newbie questions. thanks "Tom Ogilvy" wrote: No, the macro can be placed in the personal.xls file and executed on the active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
the code is in a workbook on your computer (personal.xls)
excel is hosted on your computer when you open the subject workbook from the shared drive, you either have exclusive access or open it read only. In any event, when you run the macro with that workbook open and active, the macro does nothing to the subject workbook other than activate a cell. It doesn't write anything in the workbook. -- Regards, Tom Ogilvy "acss" wrote: I work in a multi-user enviorment and the monthly workbook sent to me would be dropped into a network folder. I need to know if by using this macro, will it affect other users excell usage from their desktop or only this workbook in the network folder when they open it? Sorry for newbie questions. thanks "Tom Ogilvy" wrote: No, the macro can be placed in the personal.xls file and executed on the active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
I saved th macro and closed/reopened the workbook. Now i have a compile error
"Invalid outside procedure" It has the phrase ("Enter flight number: ") highlighted. Is there something that can fix this? "Tom Ogilvy" wrote: the code is in a workbook on your computer (personal.xls) excel is hosted on your computer when you open the subject workbook from the shared drive, you either have exclusive access or open it read only. In any event, when you run the macro with that workbook open and active, the macro does nothing to the subject workbook other than activate a cell. It doesn't write anything in the workbook. -- Regards, Tom Ogilvy "acss" wrote: I work in a multi-user enviorment and the monthly workbook sent to me would be dropped into a network folder. I need to know if by using this macro, will it affect other users excell usage from their desktop or only this workbook in the network folder when they open it? Sorry for newbie questions. thanks "Tom Ogilvy" wrote: No, the macro can be placed in the personal.xls file and executed on the active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
It looks like you may have missed the first line:
Sub SearchSheets() when you did your copy|paste. acss wrote: I saved th macro and closed/reopened the workbook. Now i have a compile error "Invalid outside procedure" It has the phrase ("Enter flight number: ") highlighted. Is there something that can fix this? "Tom Ogilvy" wrote: the code is in a workbook on your computer (personal.xls) excel is hosted on your computer when you open the subject workbook from the shared drive, you either have exclusive access or open it read only. In any event, when you run the macro with that workbook open and active, the macro does nothing to the subject workbook other than activate a cell. It doesn't write anything in the workbook. -- Regards, Tom Ogilvy "acss" wrote: I work in a multi-user enviorment and the monthly workbook sent to me would be dropped into a network folder. I need to know if by using this macro, will it affect other users excell usage from their desktop or only this workbook in the network folder when they open it? Sorry for newbie questions. thanks "Tom Ogilvy" wrote: No, the macro can be placed in the personal.xls file and executed on the active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
Thanks Dave,
That was it. Works great now......one last question on this. Do the users always have to go to tools --macros..run macro for the code to work? thanks again "Dave Peterson" wrote: It looks like you may have missed the first line: Sub SearchSheets() when you did your copy|paste. acss wrote: I saved th macro and closed/reopened the workbook. Now i have a compile error "Invalid outside procedure" It has the phrase ("Enter flight number: ") highlighted. Is there something that can fix this? "Tom Ogilvy" wrote: the code is in a workbook on your computer (personal.xls) excel is hosted on your computer when you open the subject workbook from the shared drive, you either have exclusive access or open it read only. In any event, when you run the macro with that workbook open and active, the macro does nothing to the subject workbook other than activate a cell. It doesn't write anything in the workbook. -- Regards, Tom Ogilvy "acss" wrote: I work in a multi-user enviorment and the monthly workbook sent to me would be dropped into a network folder. I need to know if by using this macro, will it affect other users excell usage from their desktop or only this workbook in the network folder when they open it? Sorry for newbie questions. thanks "Tom Ogilvy" wrote: No, the macro can be placed in the personal.xls file and executed on the active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
Running the macro works but when trying hitting the continue button it closes
the dialog box having the user constantly opening the run macro from the tools drop down. Is there a way when htting continue, it leaves the option open for another search entry? "Tom Ogilvy" wrote: the code is in a workbook on your computer (personal.xls) excel is hosted on your computer when you open the subject workbook from the shared drive, you either have exclusive access or open it read only. In any event, when you run the macro with that workbook open and active, the macro does nothing to the subject workbook other than activate a cell. It doesn't write anything in the workbook. -- Regards, Tom Ogilvy "acss" wrote: I work in a multi-user enviorment and the monthly workbook sent to me would be dropped into a network folder. I need to know if by using this macro, will it affect other users excell usage from their desktop or only this workbook in the network folder when they open it? Sorry for newbie questions. thanks "Tom Ogilvy" wrote: No, the macro can be placed in the personal.xls file and executed on the active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
Running the code works however when hitting the continue button in the
dialog box, it closes and does not continue for another search. What could i have done wrong in this step? thanks "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
Running the code works however when hitting the continue button in the
dialog box, it closes and does not continue for another search. What could i have done wrong in this step? thanks "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple sheet search
You can use a shortcut key (alt-f8) to get to that dialog.
You could assign a short cut key to the macro tools|macros|macro select your macro Click Options Assign it a nice shortcut key combination (stay away from excel's builtin shortcuts) Click ok Then click Cancel (to close the tools|macro dialog) There are other ways, too. acss wrote: Thanks Dave, That was it. Works great now......one last question on this. Do the users always have to go to tools --macros..run macro for the code to work? thanks again "Dave Peterson" wrote: It looks like you may have missed the first line: Sub SearchSheets() when you did your copy|paste. acss wrote: I saved th macro and closed/reopened the workbook. Now i have a compile error "Invalid outside procedure" It has the phrase ("Enter flight number: ") highlighted. Is there something that can fix this? "Tom Ogilvy" wrote: the code is in a workbook on your computer (personal.xls) excel is hosted on your computer when you open the subject workbook from the shared drive, you either have exclusive access or open it read only. In any event, when you run the macro with that workbook open and active, the macro does nothing to the subject workbook other than activate a cell. It doesn't write anything in the workbook. -- Regards, Tom Ogilvy "acss" wrote: I work in a multi-user enviorment and the monthly workbook sent to me would be dropped into a network folder. I need to know if by using this macro, will it affect other users excell usage from their desktop or only this workbook in the network folder when they open it? Sorry for newbie questions. thanks "Tom Ogilvy" wrote: No, the macro can be placed in the personal.xls file and executed on the active workbook. If you don'thave a personal.xls already, go to Tools=Macro=Record a Macro and then select to save the macro in the personal workbook. Then select a cell or two and turn off macro recording. This will create the personal.xls file. This is a standard workbook stored in the xlStart directory and opens as a hidden window whenever excel is opened manually. Macros placed here will be visible when you do Tools=Macro=Macros. PERSONAL.XLS!SearchSheets More sheets would not be a problem as the macro makes no assumption about number of sheets If multiple columns to look at you could do this Sub SearchSheets() Dim ans As String, rng As Range Dim sh As Worksheet, saddr As String Dim res As Long Const col as String = "H:H,J:J" ans = InputBox("Enter flight number: ") If Len(Trim(ans)) = 0 Then Exit Sub For Each sh In Worksheets set rng1 = sh.Range(col) Set rng = _ rng1.Find(What:=ans, _ After:=rng1(1), _ 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 = rng1.FindNext(rng) Loop While rng.Address < saddr End If If res = vbNo Then Exit For Next End Sub change Const col as String = "H:H,J:J" to reflect the columns that need to be examined. -- Regards, Tom Ogilvy "acss" wrote: Thanks Tom, I tried the find after grouping and it works but the manual process of grouping can be a drag. In the option of creating the macro, I gues it would have to be inserted into the new workbook sent to me monthly. In using the macro, what change is needed if there is a different column or additional sheets? Thanks for the help "Tom Ogilvy" wrote: You could group all the sheet and to Edit=Find if you want a macro: 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 -- regards, Tom Ogilvy "acss" wrote: I have a workbook that that is sent to me on a monthly basis and it contains 8 sheets of data. I spent a huge amount of time searching flight numbers on column H. Is there a way to automate the search so i do not have to go to each sheet and search column H row by row? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"search" a different sheet with multiple criteria | Excel Worksheet Functions | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
Search text in multiple files in multiple directories | Excel Programming | |||
Search multiple sheets, then paste results in new sheet | Excel Programming | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions |