How do I search for #N/A in entire workbook?

[Nakis]

Hi all,
we are importing some FX rates into an Excel2K workbook (consisting of 20 worksheets).
Some cells update to #N/A and we manually have to look for them w/s by w/s.
Is there a macro or VB code we can use to identify which w/s and which cells contain the #N/A easily?

thanx

 

[DPlank]

You can use a formula :

=IF(ISNA(A1), 1, 0)

Will return 1 if contents of cell A1 is a #N/A error.

Adjust the ranges (and maybe return more meaningful values and you're done.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[dhulbert]

Nakis

Just use the Edit-Find Command

Search for #N/A
Within Workbook
Lookin Values

Keep pressing F3 and it keeps stepping to the next one.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Nakis]

Hi again,

DPlank
if I include a range in ISNA then it won't return 0 unless all cells in range are #NA

dhulbert
Edit->Replace in Excel2K does not give the option to look in entire workbook. It onle searches active worksheet.

thanx

 

[DPlank]

Put the formula in 1 cell and copy it down through the range you want to cover. Look up Fill Down in the help for further info on this.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[Nakis]

DPlank,
the ranges in each of my sheets is B5 to M40.
The #N/A can appear anywhere within this range, in any of the 20 sheets in the same workbook.
What would be preferable is not to have a formula for each cell that could possibly have a #N/A, but a convenient way to identify the #N/As.
For example a new blank sheet that will print the sheet name and the cell that contains the #N/A.

thanx

 

[xlbo]

If you are on 2000, there is no manual way to search the entire workbook

If you want help with writing code, please ask in the VBA forum (Forum707)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question