Loop Through All Open Workbooks

[scott]

I'm trying to find an example of looping through all open workbooks and
doing a simple search of all cells to determine if a certain value exists in
any of the workbooks.

Any help?

 

[Tom Ogilvy]

Dim bk as Workbook, sh as Worksheet
Dim rng as Range, ans as long
for each bk in application.Workbooks
for each sh in bk.Worksheets
set rng = sh.cells.Find("string")
if not rng is nothing then
msgbox "Found at " & rng.Address(external:=True)
ans = msgbox "Continue to search",vbYesNo
if ans = vbNo then exit sub
end if
Next
Next

 

[scott]

Thank you again for saving sanity.

Dim bk as Workbook, sh as Worksheet
Dim rng as Range, ans as long
for each bk in application.Workbooks
for each sh in bk.Worksheets
set rng = sh.cells.Find("string")
if not rng is nothing then
msgbox "Found at " & rng.Address(external:=True)
ans = msgbox "Continue to search",vbYesNo
if ans = vbNo then exit sub
end if
Next
Next

 

[Bob Phillips]

For Each wb In Application.Workbooks
If Application.CountIf(wb.Worksheets(1).Cells,"myValue) > 1 Then
MsgBox "Found in " & wb.Name
End If
Next wb

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[scott]

tom, can you look at the line

ans = msgbox "Continue to search",vbYesNo

it has a syntax error. thanks

 

[scott]

can you correct syntax error in line

If Application.CountIf(wb.Worksheets(1).Cells,"myValue) > 1 Then


thanks

 

[Bob Phillips]

ans = msgbox( "Continue to search",vbYesNo)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

If Application.CountIf(wb.Worksheets(1).Cells,"myValue") > 1 Then


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

ans = msgbox( "Continue to search",vbYesNo)

 

[Tom Ogilvy]

If Application.CountIf(wb.Worksheets(1).Cells,"myValue") > 1 Then

 

[scott]

i ran your code and it doesn't find a particular cell or give any error. can
you try again?

 

[Tom Ogilvy]

You have a solution that does that.