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

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