| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
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" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
Thank you again for saving sanity.
"Tom Ogilvy" wrote in message ... 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" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
For Each wb In Application.Workbooks If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then MsgBox "Found in " & wb.Name End If Next wb -- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
tom, can you look at the line
ans = msgbox "Continue to search",vbYesNo it has a syntax error. thanks "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
can you correct syntax error in line
If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then thanks "Bob Phillips" wrote in message ... For Each wb In Application.Workbooks If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then MsgBox "Found in " & wb.Name End If Next wb -- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
ans = msgbox( "Continue to search",vbYesNo)
-- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... tom, can you look at the line ans = msgbox "Continue to search",vbYesNo it has a syntax error. thanks "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
If Application.CountIf(wb.Worksheets(1).Cells,"myValu e") 1 Then
-- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... can you correct syntax error in line If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then thanks "Bob Phillips" wrote in message ... For Each wb In Application.Workbooks If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then MsgBox "Found in " & wb.Name End If Next wb -- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... 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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
ans = msgbox( "Continue to search",vbYesNo)
-- regards, Tom Ogilvy "scott" wrote in message ... tom, can you look at the line ans = msgbox "Continue to search",vbYesNo it has a syntax error. thanks "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
If Application.CountIf(wb.Worksheets(1).Cells,"myValu e") 1 Then
-- Regards, Tom Ogilvy "scott" wrote in message ... can you correct syntax error in line If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then thanks "Bob Phillips" wrote in message ... For Each wb In Application.Workbooks If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then MsgBox "Found in " & wb.Name End If Next wb -- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... 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? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
i ran your code and it doesn't find a particular cell or give any error. can
you try again? "Bob Phillips" wrote in message ... For Each wb In Application.Workbooks If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then MsgBox "Found in " & wb.Name End If Next wb -- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... 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? |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Loop Through All Open Workbooks
You have a solution that does that.
-- Regards, Tom Ogilvy "scott" wrote in message ... i ran your code and it doesn't find a particular cell or give any error. can you try again? "Bob Phillips" wrote in message ... For Each wb In Application.Workbooks If Application.CountIf(wb.Worksheets(1).Cells,"myValu e) 1 Then MsgBox "Found in " & wb.Name End If Next wb -- HTH RP (remove nothere from the email address if mailing direct) "scott" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
| Loop through all Open workbooks | Excel Programming | |||
| Loop through open workbooks | Excel Programming | |||
| IF wokkbook1 is open, open workbook2 Loop | Excel Programming | |||
| Can I have a loop to open a set of workbooks get some data, close it one a time. | Excel Programming | |||
Linear Mode
