VBA to search across multiple sheets?

Francis Hayes

unread,

May 3, 1998, 3:00:00 AM5/3/98

to

Excel can't search across multiple sheets - I find this very
frustrating. I figure that someone who is very familiar with VBA should
be able to write a fairly simple macro to do this. I am relatively new
to VBA and have attempted to do it a couple of times but I always got
stuck. Ideally (if possible) it would search in both sheets and modules.

Can anyone help? I'm sure there are a lot of others who could use this.

Thanks in advance,

Francis Hayes
Newfoundland, Canada

Bob Flanagan

unread,

May 3, 1998, 3:00:00 AM5/3/98

to

Just select the sheets first and then do the search. It works every time
for me <g>.

Bob Flanagan email: bobf at dol.net
Macro Systems
http://www.add-ins.com - Productivity Add-ins, VB code, and Excel Links

Francis Hayes <fha...@nf.sympatico.ca> wrote in article
<354C66...@nf.sympatico.ca>...

John Green

unread,

May 4, 1998, 3:00:00 AM5/4/98

to

Hi Bob,

You must be using Excel 5. Edit, Find does not work across grouped
sheets in Excel 95/97. The following code will search all
(not just grouped) worksheets for specified data:

Sub WorkbookFind()
What = InputBox("What are you looking for")
For Each sht In Worksheets
sht.Activate
Set Found = sht.Cells.Find(What)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Found.Activate
Response = MsgBox("Continue", vbYesNo + vbQuestion)
If Response = vbNo Then Exit Sub
Set Found = Cells.FindNext(After:=ActiveCell)
If Found.Address = FirstAddress Then Exit Do
Loop
End If
Next sht
End Sub

HTH,

John Green - Excel MVP
Sydney
Australia

Francis Hayes

unread,

May 10, 1998, 3:00:00 AM5/10/98

to

Hi John,

Thanks for the code. I've been waiting a long time to get this. The
first time I tested it (on a workbook with 40 sheets) I searched and
found something I had been looking for a long while.

Now, to make this code perfect it would also search modules. You see,
I'm just learning VBA and I'm collecting sample code from everywhere and
saving it in module sheets in an Excel 95 workbook - sort of my own VBA
reference library. Many times I know I have the right piece of code
somewhere but it is hard to find looking sheet by sheet. I've had a few
e-mails from others interested in the same sort of code. Is it possible
to have your code search across modules?

Thanks in advance,
Francis Hayes
Newfoundland, Canada

Come visit my corner of the globe ...
http://www.newcomm.net/grosmorne/

John Green

unread,

May 14, 1998, 3:00:00 AM5/14/98

to

In article <355771...@nf.sympatico.ca>, Francis Hayes wrote:
> Is it possible
> to have your code search across modules?
>

Hi Francis,

Searching across modules is much more complex than searching
worksheets. In Excel 5/95 you have to export the modules to text files
and then search the text files using VBA code. You can't use the Find
method with a module.

Excel 97 has more sophisticated techniques available but, once again,
the coding is complex.

I have not written any code to do what you want and don't know of any
available code. If you want to see code that handles text in modules,
the best I know is in an application which cleans code modules - by Rob
Bovey - and an extension of this which indents code by Stephen Bullen.
You can see Rob's code at www.baarns.com and Stephen's at
www.bmsltd.co.uk,

Regards,