Monday, June 30, 2008

Extract Error Cells in a Sheet using Excel VBA

Identify Error Cells in a Sheet using Excel VBA

The following code can be used to identify cells that contains error because of some formula

Sub Rows_Wt_Number_Errors()


Dim oNOCells

On Error GoTo Err_Hdlr
Set oNOCells = Range("A1:B5").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

For Each ocell In oNOCells
MsgBox ocell.Address
Next ocell

Err_Hdlr:
If Err <> 0 Then
If Err.Description = "No cells were found." Then
MsgBox "No cells with number in forumula found"
End If
Err.Clear

End If

End Sub


The above code will be useful to identify errors like #DIV/0! Error in Excel VBA, #N/A Error in Excel VBA

2 comments:

  1. Anonymous8:10 AM

    This was very helpful for me. I modified it to use the current selection as the range to search, and it automatically jumps to the first error. This helped me A LOT today!

    ReplyDelete
  2. an cell with error as value will not be identified thru this code

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.