Listing Using Error Handling When Using Special Cells

Sub SpecialCells()

Dim ws As Worksheet Dim rgSpecial As Range Dim rgCell As Range

On Error Resume Next

Set ws = ThisWorkbook.Worksheets("Special Cells")

Set rgSpecial = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

If Not rgSpecial Is Nothing Then rgSpecial.Interior.Color = vbRed


MsgBox "Congratulations! " & ws.Name & _ " is an error-free worksheet."

End If

Set rgSpecial = Nothing Set rgCell = Nothing Set ws = Nothing End Sub

All this procedure does is look for any errors on a worksheet named Special Cells. If it finds any errors, it makes the background of the cells that contain an error red. Otherwise the procedure reports that it didn't find any errors.

The first thing this procedure needs to do is enable error handling. In this case, you instruct VBA to simply move to the following statement if a run-time error occurs. If SpecialCells generates an error, the Range variable rgSpecial never gets initialized. Using the Is Nothing statement, you can see if this is the case and take the appropriate action.

WARNING A run-time error (error number 1004: No Cells Were Found.) occurs if the SpecialCells method doesn't find any special cells. Consequently, you need to use error handling when you use this method.

0 0

Post a comment