| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple worksheets search
Hello people,
i'm not a programmer and i know only a few things about VB. My problem is this: i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this: Private Sub FindCB_Click() 'This is the Find Next button in my form Dim sh As Worksheet Dim rng As Range, firstAddress As String For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then firstAddress = rng.Address Do If Not rng Is Nothing Then Application.Goto rng, True End If Set rng = sh.Cells.FindNext(rng) Loop Until (rng.Address = firstAddress) End If Next So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel? Thank you very much in advance! --- Message posted from http://www.ExcelForum.com/ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple worksheets search
If you have excel 2000 or later, you don't need your own special button.
just group the sheets before you do the search and use the classic find button. -- Regards, Tom Ogilvy "GTS " wrote in message ... Hello people, i'm not a programmer and i know only a few things about VB. My problem is this: i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this: Private Sub FindCB_Click() 'This is the Find Next button in my form Dim sh As Worksheet Dim rng As Range, firstAddress As String For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then firstAddress = rng.Address Do If Not rng Is Nothing Then Application.Goto rng, True End If Set rng = sh.Cells.FindNext(rng) Loop Until (rng.Address = firstAddress) End If Next So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel? Thank you very much in advance! --- Message posted from http://www.ExcelForum.com/ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple worksheets search
Hi
In Excel 2002/2003, there is a "Within:" option in the Find/Replace dialog. You can choose workbook. You don't have to select all sheets then like Tom suggested -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Tom Ogilvy" wrote in message ... If you have excel 2000 or later, you don't need your own special button. just group the sheets before you do the search and use the classic find button. -- Regards, Tom Ogilvy "GTS " wrote in message ... Hello people, i'm not a programmer and i know only a few things about VB. My problem is this: i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this: Private Sub FindCB_Click() 'This is the Find Next button in my form Dim sh As Worksheet Dim rng As Range, firstAddress As String For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then firstAddress = rng.Address Do If Not rng Is Nothing Then Application.Goto rng, True End If Set rng = sh.Cells.FindNext(rng) Loop Until (rng.Address = firstAddress) End If Next So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel? Thank you very much in advance! --- Message posted from http://www.ExcelForum.com/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple worksheets search
Does it work if you do group the sheets and don't make a "Within" selection?
-- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi In Excel 2002/2003, there is a "Within:" option in the Find/Replace dialog. You can choose workbook. You don't have to select all sheets then like Tom suggested -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Tom Ogilvy" wrote in message ... If you have excel 2000 or later, you don't need your own special button. just group the sheets before you do the search and use the classic find button. -- Regards, Tom Ogilvy "GTS " wrote in message ... Hello people, i'm not a programmer and i know only a few things about VB. My problem is this: i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this: Private Sub FindCB_Click() 'This is the Find Next button in my form Dim sh As Worksheet Dim rng As Range, firstAddress As String For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then firstAddress = rng.Address Do If Not rng Is Nothing Then Application.Goto rng, True End If Set rng = sh.Cells.FindNext(rng) Loop Until (rng.Address = firstAddress) End If Next So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel? Thank you very much in advance! --- Message posted from http://www.ExcelForum.com/ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple worksheets search
Hi Tom
Yes it is working then -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Does it work if you do group the sheets and don't make a "Within" selection? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi In Excel 2002/2003, there is a "Within:" option in the Find/Replace dialog. You can choose workbook. You don't have to select all sheets then like Tom suggested -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Tom Ogilvy" wrote in message ... If you have excel 2000 or later, you don't need your own special button. just group the sheets before you do the search and use the classic find button. -- Regards, Tom Ogilvy "GTS " wrote in message ... Hello people, i'm not a programmer and i know only a few things about VB. My problem is this: i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this: Private Sub FindCB_Click() 'This is the Find Next button in my form Dim sh As Worksheet Dim rng As Range, firstAddress As String For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then firstAddress = rng.Address Do If Not rng Is Nothing Then Application.Goto rng, True End If Set rng = sh.Cells.FindNext(rng) Loop Until (rng.Address = firstAddress) End If Next So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel? Thank you very much in advance! --- Message posted from http://www.ExcelForum.com/ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Multiple worksheets search
Thanks for testing.
Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi Tom Yes it is working then -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Tom Ogilvy" wrote in message ... Does it work if you do group the sheets and don't make a "Within" selection? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi In Excel 2002/2003, there is a "Within:" option in the Find/Replace dialog. You can choose workbook. You don't have to select all sheets then like Tom suggested -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Tom Ogilvy" wrote in message ... If you have excel 2000 or later, you don't need your own special button. just group the sheets before you do the search and use the classic find button. -- Regards, Tom Ogilvy "GTS " wrote in message ... Hello people, i'm not a programmer and i know only a few things about VB. My problem is this: i wanted to create a macro where i would be able to perform the search action in all worksheets of an excel workbook. So, i created a "Find All" menu item and when it is clicked a search-like form is opened. The search is performed like this: Private Sub FindCB_Click() 'This is the Find Next button in my form Dim sh As Worksheet Dim rng As Range, firstAddress As String For Each sh In ThisWorkbook.Worksheets Set rng = sh.Cells.Find(What:="*" & FindAllForm.TextBox1.Text & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rng Is Nothing Then firstAddress = rng.Address Do If Not rng Is Nothing Then Application.Goto rng, True End If Set rng = sh.Cells.FindNext(rng) Loop Until (rng.Address = firstAddress) End If Next So, when the code is executed it goes directly into the last string it finds, skipping all the intermediate strings. How can i make it stop in every string it finds? And then by clicking the button to go to the next one? Like the classic Find option in excel? Thank you very much in advance! --- Message posted from http://www.ExcelForum.com/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search Multiple Worksheets | Excel Discussion (Misc queries) | |||
| Search in Multiple Worksheets | Excel Discussion (Misc queries) | |||
| Search multiple worksheets | Excel Worksheet Functions | |||
| Search multiple worksheets - Excel 97 | Excel Discussion (Misc queries) | |||
| Search Multiple Worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
