Check if the Excel file contain error

T

TPortsmouth

New Member
Joined
Apr 6, 2017
Messages
41
Hello Excel expert,

I am trying to build a startup VBA to check if the current worksheet contain any error as below:

#VALUE !
#DIV/0!
#N/A

This VBA will check which cells contain above error and would prompt a msgbox to show info like below:
Total 10 errors found!
Cell A1, A3, B3 contain #VALUE ! error.
Cell A1, A3, B3 contain #DIV/0! error.
Cell A1, A3, B3 contain #N/A! error.
​

Where the red highlighted text are variables.

Do you have any clue for this? Thanks in advance.

Regards,

TPortsmouth
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
This should get you started:
Code: 
If VarType(ActiveCell.Value) <> vbError Then   
 ' do something
End If
 
Last edited:
Upvote 0
This will list out each cell with an error. If you want the cells listed by error, you'll have to test the error and create separate lists for each.... I'll leave that up to you...

Also, this will check all sheets in the workbook.


Code: 
Sub FindErrors()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim msg As String
        
    For Each ws In ActiveWorkbook.Worksheets
        Set rng = Nothing
        On Error Resume Next
        Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
        On Error GoTo 0
        If Not rng Is Nothing Then
            msg = "Total " & rng.Count & " errors found..." & vbCrLf
            For Each cell In rng
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Next
            MsgBox msg, vbCritical, Application.ActiveWorkbook.Name
        End If
    Next
End Sub
 
Upvote 0
Hi PatOBrien,

Thanks for your advise. I've tried your provided code, however, as I am very green in VBA, can you further guide me as below:


  • How to specific the active worksheet?
  • How to define the range? Should I change Set rng = Nothing into Set rng = Range ("A1:A10")?
  • The code will return all cell's value, how do I specific only certain error to display?

Thanks.

PatOBrien198 said:
This will list out each cell with an error. If you want the cells listed by error, you'll have to test the error and create separate lists for each.... I'll leave that up to you...

Also, this will check all sheets in the workbook.


Code: 
Sub FindErrors()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim msg As String
        
    For Each ws In ActiveWorkbook.Worksheets
        Set rng = Nothing
        On Error Resume Next
        Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
        On Error GoTo 0
        If Not rng Is Nothing Then
            msg = "Total " & rng.Count & " errors found..." & vbCrLf
            For Each cell In rng
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Next
            MsgBox msg, vbCritical, Application.ActiveWorkbook.Name
        End If
    Next
End Sub
Click to expand...
 
Upvote 0
Code: 
Sub FindErrors2()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim msg As String
    Dim rngToSearch As Range
    Dim iErrorCount As Integer
    
    'to hard code the range to seach for errors
    Set rngToSearch = Range("A1:G25")
    
    'to let the user select the cells to search uncomment the following line
'    Set rngToSearch = Application.InputBox("Select range to find errors...", Type:=8)
    
    Set ws = ActiveSheet
    Set rng = Nothing
    On Error Resume Next
    'to search all cells in current worksheet uncomment following line and comment out next set line
'    Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    'to search only the defined range
    Set rng = rngToSearch.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    On Error GoTo 0
    iErrorCount = 0
    If Not rng Is Nothing Then
        For Each cell In rng
            Select Case cell.Text
            'comment out those Case clauses (2 lines) that you don't want to include in the results
            Case "#VALUE!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case "#DIV/0!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case "#N/A"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case "#REF!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case Else
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
                MsgBox "New error to code for..." & cell.Text
            End Select
        Next
        
        msg = "Total " & iErrorCount & " errors found..." & vbCrLf & msg
        MsgBox msg, vbCritical, Application.ActiveWorkbook.Name
    End If
End Sub
 
Upvote 0
Hi PatOBrien198,

Great thanks! It works!

I still have 3 minor requirement on below VBA:

  1. How to create another IF and msg if no error found?
    That is if no error found, show below Msg
    "No error found."
  2. For the displayed cell, can the cell reference change from absolute to relative reference?
    That is to remove the $ sign.
  3. If the file contains a lot of error, I realized the Msg box can show around 30 lines, can I limit the message to 30 lines?
    And if the total record exceed 30 lines, Msg box show "There are still more errors!"
I just figure out how to make this run automatically by changing the sub name into Sub Auto_Open()

Thanks.

TPortsmouth

PatOBrien198 said:
Code: 
Sub FindErrors2()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim msg As String
    Dim rngToSearch As Range
    Dim iErrorCount As Integer
    
    'to hard code the range to seach for errors
    Set rngToSearch = Range("A1:G25")
    
    'to let the user select the cells to search uncomment the following line
'    Set rngToSearch = Application.InputBox("Select range to find errors...", Type:=8)
    
    Set ws = ActiveSheet
    Set rng = Nothing
    On Error Resume Next
    'to search all cells in current worksheet uncomment following line and comment out next set line
'    Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    'to search only the defined range
    Set rng = rngToSearch.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    On Error GoTo 0
    iErrorCount = 0
    If Not rng Is Nothing Then
        For Each cell In rng
            Select Case cell.Text
            'comment out those Case clauses (2 lines) that you don't want to include in the results
            Case "#VALUE!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case "#DIV/0!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case "#N/A"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case "#REF!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
            Case Else
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & cell.Address & " contains " & cell.Text & vbCrLf
                MsgBox "New error to code for..." & cell.Text
            End Select
        Next
        
        msg = "Total " & iErrorCount & " errors found..." & vbCrLf & msg
        MsgBox msg, vbCritical, Application.ActiveWorkbook.Name
    End If
End Sub
Click to expand...
 
Upvote 0
Code: 
Sub FindErrors2()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim msg As String
    Dim rngToSearch As Range
    Dim iErrorCount As Integer
    Dim sCellAddress As String
    
    'to hard code the range to seach for errors
    Set rngToSearch = Range("A1:G25")
    
    'to let the user select the cells to search uncomment the following line
'    Set rngToSearch = Application.InputBox("Select range to find errors...", Type:=8)
    
    Set ws = ActiveSheet
    Set rng = Nothing
    On Error Resume Next
    'to search all cells in current worksheet uncomment following line and comment out next set line
'    Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    'to search only the defined range
    Set rng = rngToSearch.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    On Error GoTo 0
    iErrorCount = 0
    If Not rng Is Nothing Then
        For Each cell In rng
            sCellAddress = Replace(cell.Address, "$", "")
            Select Case cell.Text
            'comment out those Case clauses (2 lines) that you don't want to include in the results
            Case "#VALUE!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
            Case "#DIV/0!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
            Case "#N/A"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
            Case "#REF!"
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
            Case Else
                iErrorCount = iErrorCount + 1
                msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
                MsgBox "New error to code for..." & cell.Text
            End Select
            If iErrorCount >= 3 Then
                Exit For
            End If
        Next
    End If
    If iErrorCount = 0 Then
        msg = "No Errors Found!"
    Else
        If rng.Count > iErrorCount Then
            msg = "Showing " & iErrorCount & " errors out of total: " & rng.Count & " errors." & vbCrLf & msg
        Else
            msg = "Total " & rng.Count & " errors found..." & vbCrLf & msg
        End If
    End If
    MsgBox msg, vbCritical, Application.ActiveWorkbook.Name
End Sub
 
Upvote 0
Hi PatOBrien198,

Thanks for your suggestion, this help a lot!

I just found another issue. If the error falls in a merged cell, your code would not recognize this type of error.

Can you further handle this case?

Thanks.

TPostsmouth
 
Upvote 0
Ok, try this...

Code: 
Sub FindErrors2()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim msg As String
    Dim rngToSearch As Range
    Dim iErrorCount As Integer
    Dim sCellAddress As String
    Dim iMergedCellCount As Integer
    
    'to hard code the range to seach for errors
    Set rngToSearch = Range("A1:G25")
    
    'to let the user select the cells to search uncomment the following line
'    Set rngToSearch = Application.InputBox("Select range to find errors...", Type:=8)
    
    Set ws = ActiveSheet
    Set rng = Nothing
    On Error Resume Next
    'to search all cells in current worksheet uncomment following line and comment out next set line
'    Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    'to search only the defined range
    Set rng = rngToSearch.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    On Error GoTo 0
    iErrorCount = 0
    iMergedCellCount = 0
    If Not rng Is Nothing Then
        For Each cell In rng
            If IsError(cell) Then
            'If cell.Value <> "" Then
                sCellAddress = Replace(cell.Address, "$", "")
                Select Case cell.Text
                'comment out those Case clauses (2 lines) that you don't want to include in the results
                Case "#VALUE!"
                    iErrorCount = iErrorCount + 1
                    msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
                Case "#DIV/0!"
                    iErrorCount = iErrorCount + 1
                    msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
                Case "#N/A"
                    iErrorCount = iErrorCount + 1
                    msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
                Case "#REF!"
                    iErrorCount = iErrorCount + 1
                    msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
                Case Else
                    iErrorCount = iErrorCount + 1
                    msg = msg & "Cell " & sCellAddress & " contains " & cell.Text & vbCrLf
                    MsgBox "New error to code for..." & cell.Text
                End Select
                If iErrorCount >= 3 Then
                    Exit For
                End If
            Else
                iMergedCellCount = iMergedCellCount + 1
            End If
        Next
    End If
    If iErrorCount = 0 Then
        msg = "No Errors Found!"
    Else
        If rng.Count - iMergedCellCount > iErrorCount Then
            msg = "Showing " & iErrorCount & " errors out of total: " & rng.Count - iMergedCellCount & " errors." & vbCrLf & msg
        Else
            msg = "Total " & rng.Count - iMergedCellCount & " errors found..." & vbCrLf & msg
        End If
    End If
    MsgBox msg, vbCritical, Application.ActiveWorkbook.Name
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA - For Cycle with "On Error" not working on the second iteration
Replies
9
Views
108
Joe4
Joe4
A
  • Question Question
Compile Error
Replies
7
Views
332
Joe4
Joe4
P
  • Solved
Dynamic cell reference for use with Index function
Replies
2
Views
215
Prudent
P
G
  • Solved
run VBA when clicked
Replies
4
Views
95
Gylle
G
O
  • Solved
MS Excel VBA Code to Delete a Row if the Cells Contains a Partial Match for Values Listed in An Array
Replies
6
Views
343
OilEconomist
O

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top