Multiple worksheets search

G

GTS

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!
 
T

Tom Ogilvy

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.
 
R

Ron de Bruin

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)




Tom Ogilvy said:
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.
 
T

Tom Ogilvy

Does it work if you do group the sheets and don't make a "Within" selection?

--
Regards,
Tom Ogilvy

Ron de Bruin said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top