Edit : Find....not working between sheets

M

medicchic

OK... I must admit I do not know how to use that code. Where do I plac
it? Thanks. Caroly
 
D

Dave Peterson

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Short course:
Open your workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out--Tools|macro|macros...|Run
SearchAllSheets

===
And watch out for wordwrap in Jim's post.

Move "strSearchString & _ onto this line:
returnValue = MsgBox("In " & ActiveSheet.Name & " Found " &
 
J

Jim May

Select the code I previously provided you:

starting with Sub SearchAllSheets()
and down through End Sub

then do a Control-Copy (Ctl-C)

Switch to your workbook in excel (With your workbook open),
Hold down the Alt key and simultaneously press the F11 key
The VBE Window should suddenly appear;
The the tree structure window (Project explorer) is on the left-hand side.
In that window click (once) on the object VBAProject(yourbookname)
Then go to the menu and select Insert: then select module;
Your cursor, at this point should be in the Code Window

At this point do a Ctl-V (or Paste)

Now the code is in the (standard) module.

Close the VBE Window.

Back at your first worksheet (sheet1 or whatever name, say sheet1) now you
are ready to run the code (macro).

Simultaneously, hold down the Alt key and press F8 - this brings up the
Macro Dialog box; look for the macro named SearchAllSheets, single-click
it(select-it)
and click the Run button (on right-habd side of same box;

That should do it (each time you want or need to perform a search).

You can assign the Macro to an Icon on your Toolbar if you like to speed up
the select and run process..

HTH
 
Top