searching multiple sheets

Posted by anno on December 03, 2001 5:43 PM

i have been trying to search multiple sheets in a workbook for a value (using ctrl+f) but no matter what i do i can only get excel to search one sheet. i have tried selecting all sheets using 'select all sheets' on the shortcut menu, using shift or ctrl to select multiple sheets, and have experimented with searching for values in different formats but nothing seems to work. i am using excel 97 sr-2 and nt4. has anyone else had this problem? any ideas?

Posted by Paul on December 03, 2001 6:54 PM

I also can't get it to work with Excel '97

Posted by Ed on December 04, 2001 5:02 AM

Re: I also can't get it to work with Excel '97

Anno/Paul,

I think this is what you need. It was written by Dave @ Ozgrid (remember him?). It needs to be put in as a UDF.

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num, Range_look)
'Written by OzGrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = _
WorksheetFunction.VLookup _
(Look_Value, Tble_Array _
, Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

Posted by anno on December 04, 2001 8:16 PM

thanks. i don't speak vba though - how do you put it in as a udf? (nt)

Posted by Ed on December 05, 2001 4:48 AM

Re: thanks. i don't speak vba though - how do you put it in as a udf? (nt)

Anno,

Even though you do not know VBA, it is fairly simple to do.
1.) Open the Workbook you would like to use this in.
2.) Open the VB Editor(alt+F11).
3.) On the left side you should see something that says VBAProject(YourFileNameHere).
4.) Right click on that name and select Insert/Module. You will see a nem Module listed for your project.
5.) Copy and paste the above Function into that Module. At this point you can close the VB Editor.
6.) Now you can activate the Paste Function (Shift+F3) and scroll down the left side to "User Defined". Once you select that, you will see all the User Defined Functions on the right side. Select the one called VlookAcrossSheets and you should be able to use it like any other Function.
7.) If you have any other problems or questions you can email me at eslaski@victaulic.com. That way I can reply back with a File and we wont keep sinking on the board.

Hope This Helps,

Posted by anno on December 05, 2001 6:42 PM

thanks ed...

...looks pretty straightforward. i hope dave's solution works better than microsoft's (their 'knowledge base' entries on this problem show that bureaucracies are the same everywhere - they have a fault notice with a workaround, and another notice to tell you the workaround doesn't work, but suggest another workaround, but that's for spell checking, not searching!), but knowing him i'm sure it will. haven't seen him on this page for a while - one argument too many with mark w i think! ;) but if you're interested in keeping up with his contributions he does occasionally appear in the help talk excel page -www.helptalk.net/officeapps/.
thanks again
anno