PDA

View Full Version : Checking for Excel Errors



Dannz
10-03-2005, 04:33 PM
If there is #N/A in a cell (generated from a forumla error) is it possible to detect that in VBA so a 0 can be put in a cell

Marlboro
10-03-2005, 05:21 PM
You can use the ISERROR function to detect the error, and mix it with an if statement to get the 0.

The example below returns 0 if there is an error in cell C7, or the value of cell C7:

=IF(ISERROR(C7),0,C7)

You can call this from VBA with application.iserror

cheers

Parry
10-03-2005, 07:08 PM
Hi there is also an NA function as well to spot errors. Use this as Malboro has described in an if statement. It is best to correct the formulas themselves rather than change the value to 0 but if you wanted code that does it then heres an example.

The following code goes in the sheet module (right click then view code and paste in right hand window) and runs whenever Excel does a calculation.


Private Sub Worksheet_Calculate()
Dim Rng As Range, c As Range

'Set a range representing cells with formulas
Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las)

'Loop through the range
For Each c In Rng
'If there is an error then...
If IsError(c) Then
'Make value 0
c.Value = 0
'Colour cell yellow so you know it was changed
c.Interior.Color = vbYellow
End If
Next c

End Sub

For some reason the code tagging has placed a space in the set rng line. THere is no actual space in "xlCellTypeFormulas"

zqwerty
10-03-2005, 08:13 PM
Frequently Asked Questions About Microsoft Excel:


http://pcworld.about.com/news/Apr062001id15849.htm