MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to avoid all kind of formula error.....


Posted by NiuB on December 31, 2001 6:06 AM

such as div , #n/a or anything when it returns nothing..
is it possible to create macro like this..so the formula
that we create no need to have like iserror or is blank..
i'm thinking something like sub auto_open with this
macro..so everytime we open the file..and the formulla
returns nothing..it will shows nothing instead of error
likes #n/a, div# or 0..thanks..happy new year and hope
this possible


Posted by Rob Jackson on December 31, 2001 6:47 AM

Sub CheckForErrors()
If IsError(Range("A1").Value) Then
Range("A1").Value = ""
End If
End Sub

This piece of code checks for an error in cell A1 and if there is one removes the content of the cell. Keep in mind this will delete the formula that is returning the error, and as such may not be what you want.
If it is what you want however, it can be looped to scan a particular area. Probably easier to use the CELLS(y,x) notation rather than the A1 notation.

The code can then be activated at your request or on workbook_open.


Hope this helps.

Posted by NiuB on December 31, 2001 7:03 AM

Thanks Rob..but thats not what i intend to do..
what i like intend to do..is if there is any kind of error..
it will shows nothing and leave the formula like
it used to be...is it possible..thanks again

Posted by Ivan F Moala on December 31, 2001 1:29 PM

Have a look @
12119d.html

HTH

Ivan such as div , #n/a or anything when it returns nothing..