Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A search across multiple worksheets.
I have several worksheets that can contain upwards of 2000 rows of data. I
need to be able to search all used cells on all of these sheets looking for the #N/A error. What would be the easiest way of accomplishing this without having to run through each cell looking fo the error? I was thinking of using the ISNA function but am unsure how to accomplish this. TIA -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A search across multiple worksheets.
First, make sure only one cells is selected in each of the sheets. then
group the sheets by selecting the first sheet, then hold down the shift key and click on the tab of the last sheet to search. In the menu do: Edit=Find put in #N/A choose Options if they are not visible and make sure you choose values in the Lookin dropdown. Then FindNext your way through. This assumes xl2002 or later. -- Regards, Tom Ogilvy "Cyberwolf" wrote: I have several worksheets that can contain upwards of 2000 rows of data. I need to be able to search all used cells on all of these sheets looking for the #N/A error. What would be the easiest way of accomplishing this without having to run through each cell looking fo the error? I was thinking of using the ISNA function but am unsure how to accomplish this. TIA -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A search across multiple worksheets.
Is there a way to do this programmatically? I wil already be run VBA coding
as these sheets are heavily changed. I knew I could do it the way you suggested, and I am currently doing it that way. but I was hoping to find a way in my code to do it automatically so that I do not have to remember to do it at the end. Thanks -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf "Tom Ogilvy" wrote: First, make sure only one cells is selected in each of the sheets. then group the sheets by selecting the first sheet, then hold down the shift key and click on the tab of the last sheet to search. In the menu do: Edit=Find put in #N/A choose Options if they are not visible and make sure you choose values in the Lookin dropdown. Then FindNext your way through. This assumes xl2002 or later. -- Regards, Tom Ogilvy "Cyberwolf" wrote: I have several worksheets that can contain upwards of 2000 rows of data. I need to be able to search all used cells on all of these sheets looking for the #N/A error. What would be the easiest way of accomplishing this without having to run through each cell looking fo the error? I was thinking of using the ISNA function but am unsure how to accomplish this. TIA -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
#N/A search across multiple worksheets.
for each sh in ActiveWindow.SelectedSheets
set rng = Nothing On Error Resume Next set rng = sh.Cells.SpecialCells(xlFormulas,xlErrors) On Error goto 0 if not rng is nothing then msgbox rng.Address(0,0,xlA1,True) end if Next If you could have other than #N/A errors, then it would take more work. You never say what you want to do with these cells, so start with that. -- regards, Tom Ogilvy -- Regards, Tom Ogilvy "Cyberwolf" wrote: Is there a way to do this programmatically? I wil already be run VBA coding as these sheets are heavily changed. I knew I could do it the way you suggested, and I am currently doing it that way. but I was hoping to find a way in my code to do it automatically so that I do not have to remember to do it at the end. Thanks -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf "Tom Ogilvy" wrote: First, make sure only one cells is selected in each of the sheets. then group the sheets by selecting the first sheet, then hold down the shift key and click on the tab of the last sheet to search. In the menu do: Edit=Find put in #N/A choose Options if they are not visible and make sure you choose values in the Lookin dropdown. Then FindNext your way through. This assumes xl2002 or later. -- Regards, Tom Ogilvy "Cyberwolf" wrote: I have several worksheets that can contain upwards of 2000 rows of data. I need to be able to search all used cells on all of these sheets looking for the #N/A error. What would be the easiest way of accomplishing this without having to run through each cell looking fo the error? I was thinking of using the ISNA function but am unsure how to accomplish this. TIA -- Cyberwolf Finder of Paths, Hunter of Prey Ghost of the Night, Shadow of Day The Wolf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Multiple Worksheets | Excel Discussion (Misc queries) | |||
Search in Multiple Worksheets | Excel Discussion (Misc queries) | |||
Search multiple worksheets | Excel Worksheet Functions | |||
Search Multiple Worksheets | Excel Discussion (Misc queries) | |||
Multiple worksheets search | Excel Programming |