How to find all #N/A in workbook?

[jaryszek]

Hi,

it is possible to find all #N/A cells in Excel?
What is the best option?

Just range and find method?

Jacek

[Pete_UK]

Maybe you could think about conditional formatting, using the formula:

=ISNA(A1)

applied to all used cells in the sheet.

Hope this helps.

Pete

[jaryszek]

oo this could be nice.

So create conditional formatting though VBA, Add to each worksheet, check if conditional formatting is there and retrive cells.

Hmm i would have compare with Find method.

Best,
Jacek

[jaryszek]

After research i do not think so there is better way than find.

It would be nice if any Expert here will confirm...

Jacek

[nilem]

maybe so

Please Login or Register  to view this content.

[Pepe Le Mokko]

What do you mean exactly by " finding" ? Locating each one or counting them?

[jindon]

If you are talking about only "#N/A" error and in any Rows/Columns, Find method would be the best as long as I know.

[jaryszek]

thank you Guys,

What do you mean exactly by " finding" ? Locating each one or counting them?

Locating - like worksheet, named range there and outputting to separate worksheet as log.

On Error Resume Next: Err.Clear
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Interior.ColorIndex = 43

this will find formulas only, how this can help to find #N/As?

Jacek

[nilem]

try xlCellTypeConstants instead of xlCellTypeFormulas

[jaryszek]

What does xlCellTypeConstans means? What is constant ? Why #N/A is constant here?

Jacek

[jaryszek]

Ok i wrote something like this:

Please Login or Register  to view this content.

End Sub

then i have problem here - i am getting error 2042:

Please Login or Register  to view this content.

i suppose because setting range is also N/A.
How can i avoid this?

Please help,
Jacek

[jaryszek]

Please find example workbook.

[rorya]

Do you mean you are actually seeing an error message box that says 2042 is the error? (Error 2042 is the error value of a #N/A error but it shouldn't stop the code from working)

[Haluk]

@jaryszek, try something like this:

Please Login or Register  to view this content.

[jaryszek]

I am seeing Error 2042 and it is working.

the problem is:
Screenshot_1.png

that i want to register each rng, not only once.
So should i repeat steps for first finded to next finded?

Jacek

[jaryszek]

Ok Guys,

problem fixed.

Jacek