Loop Through All Open Workbooks

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?

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

--
Regards,
Tom Ogilvy

"scott" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

Thank you again for saving sanity.

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
>> 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, can you look at the line

ans = msgbox "Continue to search",vbYesNo

it has a syntax error. thanks


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "scott" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>
>

can you correct syntax error in line

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


thanks

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>
>

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

--
regards,
Tom Ogilvy

"scott" <[email protected]> wrote in message
news:[email protected]...
> tom, can you look at the line
>
> ans = msgbox "Continue to search",vbYesNo
>
> it has a syntax error. thanks
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "scott" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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?
> >>
> >>
> >
> >
>
>

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

--
Regards,
Tom Ogilvy


"scott" <[email protected]> wrote in message
news:[email protected]...
> can you correct syntax error in line
>
> If Application.CountIf(wb.Worksheets(1).Cells,"myValue) > 1 Then
>
>
> thanks
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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?
> >>
> >>
> >
> >
>
>

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


--

HTH

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


"scott" <[email protected]> wrote in message
news:[email protected]...
> tom, can you look at the line
>
> ans = msgbox "Continue to search",vbYesNo
>
> it has a syntax error. thanks
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "scott" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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?
> >>
> >>
> >
> >
>
>

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


--

HTH

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


"scott" <[email protected]> wrote in message
news:[email protected]...
> can you correct syntax error in line
>
> If Application.CountIf(wb.Worksheets(1).Cells,"myValue) > 1 Then
>
>
> thanks
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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?
> >>
> >>
> >
> >
>
>

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


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>>
>
>

You have a solution that does that.

--
Regards,
Tom Ogilvy

"scott" <[email protected]> wrote in message
news:ex83TP%[email protected]...
> i ran your code and it doesn't find a particular cell or give any error.
can
> you try again?
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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?
> >>
> >>
> >
> >
>
>