Home |
Search |
Today's Posts |
#1
|
|||
|
|||
using find in 2 or 3 workbooks that are open
When I have 2 or 3 seperate excel file programs open. When I do a find it
will only search that particular file. Is there a way to set it up so all excell files can be searched? Thanks |
#2
|
|||
|
|||
Nothing built into excel lets you do this.
I guess you could have a macro that searches each workbook (and each worksheet in each workbook???). Kind of like this: Option Explicit Sub testme() Dim wkbk As Workbook Dim wks As Worksheet Dim FindWhat As String Dim FirstAddress As String Dim FoundCell As Range Dim resp As Long Dim myWindow As Window Dim isVisible As Boolean Dim StopLookingNow As Boolean FindWhat = InputBox(Prompt:="Look for what:") If Trim(FindWhat) = "" Then Exit Sub End If StopLookingNow = False For Each wkbk In Application.Workbooks If StopLookingNow = True Then Exit For End If isVisible = False For Each myWindow In wkbk.Windows If myWindow.Visible Then isVisible = True Exit For End If Next myWindow If isVisible = True Then For Each wks In wkbk.Worksheets If wks.Visible = xlSheetVisible Then FirstAddress = "" With wks.UsedRange Set FoundCell = .Cells.Find(what:=FindWhat, _ after:=.Cells(.Cells.Count), LookIn:=xlFormulas, _ lookat:=xlPart, searchorder:=xlByRows, _ MatchCase:=False) If FoundCell Is Nothing Then 'do nothing Else FirstAddress = FoundCell.Address Do resp = MsgBox(Prompt:=FoundCell.Address _ (external:=True) _ & vbLf & vbLf & "Keep Looking?" _ & vbLf & _ "(yes/no/cancel = go to next workbook?", _ Buttons:=vbYesNoCancel) Select Case resp Case Is = vbNo StopLookingNow = True Exit For Case Is = vbCancel Exit For End Select Set FoundCell = .FindNext(FoundCell) Loop While Not FoundCell Is Nothing _ And FoundCell.Address < FirstAddress End If End With End If Next wks End If Next wkbk If FoundCell Is Nothing Then 'do nothing Else resp = MsgBox(Prompt:="Wanna go to the last found cell?", _ Buttons:=vbYesNo) If resp = vbYes Then Application.Goto FoundCell End If End If End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you're really industrious, you may want to look at how Jan Karel Pieterse's handles just a single workbook with FlexFind: http://www.oaltd.co.uk/MVP/ Debbie S wrote: When I have 2 or 3 seperate excel file programs open. When I do a find it will only search that particular file. Is there a way to set it up so all excell files can be searched? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel startup switches | Excel Discussion (Misc queries) | |||
Keep find box open while in spreadsheet | Excel Discussion (Misc queries) | |||
How can I make the find dialog box appear as soon as I open a spr. | Excel Discussion (Misc queries) | |||
Find (Ctrl +F) not searching all open sheets | Excel Worksheet Functions | |||
Excel Files Won't Open From Shortcut | Excel Discussion (Misc queries) |