How to hide #DIV!0 as well as #NV display in formulae 1

[karlomutschler]

hi all,

in my worksheet with ca 96000 formula I disable the display of #DIV!0 by using the appropriate IF function.

However, I still require to disable the display of #NV.

Any help much appreciated.

Kind regards
Karlo meistertools@gmx.net

 

[carrr]

use ISERROR...the Help function outlines it in detail

 

[karlomutschler]

hi carrr,

thank you for your valued response.

With the amount of formulae in the worksheet (96 000) use of ISERROR function is not an option.

I was looking for a vba script that would handle the #NV's.

Thanks
Karlo meistertools@gmx.net

 

[combo]

If you only do not want to print errors, you can set in File>Page settings... (in Sheet tab) what to print instead.

combo

 

[xlbo]

What do you want to show instead of the errors ??
do you want to clear the cells ? change the result ? make it "" ? or make it 0 ????? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[mscallisto]

I know of #div/0, #na, #name, #null, #ref, and #value but I don't know #NV. What is it?

You mention there are too many formulae in the spreadsheet to use of ISERROR is not an option.

Is this problem something that has happened recently and you don't want to change many old formula's and simply ignore the error?

Do you know why the error happens and is there perhaps another way to correct data before it hits your formula?

 

[xlbo]

I'm assuming that NV is the German / Austrian / Dutch ????
version of N/A - apologies if you are none of the above ;-)
If you are happy replacing formulae that produce errors with a value, you could do something like:

application.screenupdating = false
application.calculation = xlcalculationmanual
For each c in activesheet.usedrange.cells
if iserror(c) then
c.value = 0
else
end if
next
application.calculation = xlcalculationautomatic

Else it's gonna be along the lines of

Sub insertErrorTesting()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In ActiveSheet.UsedRange.Cells
If IsError(c) Then
tempForm = Right(c.Formula, Len(c.Formula) - 1)
newForm = "=IF(ISERROR(" & tempForm & &quot,0," & tempForm & &quot"
c.Formula = newForm
Else
End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub
which will probably take much longer
HTH Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[karlomutschler]

Hi,
Thanks everybody for your suggestions and help. You are just great !!

All I actually wanted to achieve is to get rid of the #NV (German Excel 2000).

I found a simple solution with conditional Format which does the trick.

1. Highlight the area concerned.
2. Conditional format.
3. Formula = ISERROR(C1)
4. Set Text colour to white.

Kindest regards
Karlo


meistertools@gmx.net