Detecting Errors in formulas 2

[rpg121]

I think you can do this, but I don't know how. I want to be able to detect if a cell has an error value. The cells with errors have a value of #DIV/0! in them. I think that's error code 2 in excel or Error 2007 in excel vba. I'm gonna be using it in an if statement to see if a cell has an error or not.

Thanks

 

[PaulRD]

here is one way to check for errors

If ActiveCell.Errors.Item(xlEvaluateToError).Value = _
    True Then
    MsgBox "you got a boo boo"
End If

Paul D

 

[xlbo]

This should be slightly more efficient if you are checking a lot of cells as it doesn't go as deep into the cell properties:

If IsError(ActiveCell) Then MsgBox "Error in " & ActiveCell.Address

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[PHV]

To get ALL the cells in error in the current sheet:
For Each ErrorCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
MsgBox "Error in " & ErrorCell.Address
Next

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[macropod]

Or, to consolidate all errors into a single result:
Sub ErrRpt()
Dim ErrAddr As String
ErrAddr = vbCrLf
On Error Resume Next
For Each ErrorCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
ErrAddr = ErrAddr & ErrorCell.Address & vbCrLf
Next
MsgBox "Errors in: " & ErrAddr
End Sub
Note that this will scroll off screen if there are too many errors.