#N/A search across multiple worksheets.

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.