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

Excel Not Calculating Formula

If Excel does not re-calculate the formula. That is, if the cell is not refreshed inspite of newvalues, the Calculation might not be set as automatic. To set the calculation as automatic, use the following:

Sub Switch_On_XL_Calculation()

Application.Calculation = xlCalculationAutomatic

End Sub