| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 55418 | 2005-03-10 03:33:00 | Checking for Excel Errors | Dannz (1668) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 332607 | 2005-03-10 03:33:00 | 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 | Dannz (1668) | ||
| 332608 | 2005-03-10 04:21:00 | 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 |
Marlboro (4607) | ||
| 332609 | 2005-03-10 06:08:00 | 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" |
Parry (5696) | ||
| 332610 | 2005-03-10 07:13:00 | Frequently Asked Questions About Microsoft Excel: pcworld.about.com |
zqwerty (97) | ||
| 1 | |||||