Multiple sheet search

[Guest]

I have a workbook that that is sent to me on a monthly basis and it contains
8 sheets of data. I spent a huge amount of time searching flight numbers on
column H. Is there a way to automate the search so i do not have to go to
each sheet and search column H row by row?

 

[Guest]

You could group all the sheet and to Edit=>Find

if you want a macro:

Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
ans = InputBox("Enter flight number: ")
If Len(Trim(ans)) = 0 Then Exit Sub
For Each sh In Worksheets
Set rng = _
sh.Columns(8).Find(What:=ans, _
After:=sh.Range("H65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
saddr = rng.Address
Do
Application.Goto rng, True
res = MsgBox("Continue Searching?", vbYesNo)
If res = vbNo Then Exit Do
Set rng = sh.Columns(8).FindNext(rng)
Loop While rng.Address <> saddr
End If
If res = vbNo Then Exit For
Next
End Sub

 

[Guest]

Thanks Tom,

I tried the find after grouping and it works but the manual process of
grouping can be a drag. In the option of creating the macro, I gues it would
have to be inserted into the new workbook sent to me monthly. In using the
macro, what change is needed if there is a different column or additional
sheets?

Thanks for the help

 

[Guest]

No, the macro can be placed in the personal.xls file and executed on the
active workbook. If you don'thave a personal.xls already, go to
Tools=>Macro=>Record a Macro and then select to save the macro in the
personal workbook. Then select a cell or two and turn off macro recording.
This will create the personal.xls file. This is a standard workbook stored
in the xlStart directory and opens as a hidden window whenever excel is
opened manually. Macros placed here will be visible when you do
Tools=>Macro=>Macros.

PERSONAL.XLS!SearchSheets

More sheets would not be a problem as the macro makes no assumption about
number of sheets


If multiple columns to look at you could do this


Sub SearchSheets()
Dim ans As String, rng As Range
Dim sh As Worksheet, saddr As String
Dim res As Long
Const col as String = "H:H,J:J"
ans = InputBox("Enter flight number: ")
If Len(Trim(ans)) = 0 Then Exit Sub
For Each sh In Worksheets
set rng1 = sh.Range(col)
Set rng = _
rng1.Find(What:=ans, _
After:=rng1(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
saddr = rng.Address
Do
Application.Goto rng, True
res = MsgBox("Continue Searching?", vbYesNo)
If res = vbNo Then Exit Do
Set rng = rng1.FindNext(rng)
Loop While rng.Address <> saddr
End If
If res = vbNo Then Exit For
Next
End Sub

change
Const col as String = "H:H,J:J"

to reflect the columns that need to be examined.

 

[Guest]

I work in a multi-user enviorment and the monthly workbook sent to me would
be dropped into a network folder. I need to know if by using this macro, will
it affect other users excell usage from their desktop or only this workbook
in the network folder when they open it? Sorry for newbie questions.

thanks

 

[Guest]

the code is in a workbook on your computer (personal.xls)
excel is hosted on your computer
when you open the subject workbook from the shared drive, you either have
exclusive access or open it read only. In any event, when you run the macro
with that workbook open and active, the macro does nothing to the subject
workbook other than activate a cell. It doesn't write anything in the
workbook.

 

[Guest]

I saved th macro and closed/reopened the workbook. Now i have a compile error
"Invalid outside procedure" It has the phrase ("Enter flight number: ")
highlighted. Is there something that can fix this?

 

[Dave Peterson]

It looks like you may have missed the first line:
Sub SearchSheets()
when you did your copy|paste.

 

[Guest]

Thanks Dave,
That was it. Works great now......one last question on this. Do the users
always have to go to tools --macros..run macro for the code to work?

thanks again

 

[Guest]

Running the macro works but when trying hitting the continue button it closes
the dialog box having the user constantly opening the run macro from the
tools drop down. Is there a way when htting continue, it leaves the option
open for another search entry?

 

[Guest]

Running the code works however when hitting the continue button in the
dialog box, it closes and does not continue for another search. What could i
have done wrong in this step?
thanks

 

[Guest]

Running the code works however when hitting the continue button in the
dialog box, it closes and does not continue for another search. What could i
have done wrong in this step?
thanks

 

[Dave Peterson]

You can use a shortcut key (alt-f8) to get to that dialog.
You could assign a short cut key to the macro
tools|macros|macro
select your macro
Click Options
Assign it a nice shortcut key combination
(stay away from excel's builtin shortcuts)
Click ok
Then click Cancel (to close the tools|macro dialog)

There are other ways, too.