How to quickly find formula errors in workbook using vba?

[cjindenver]

I have about 100 reports that I have to run every day, and they are automated using excel macros. There are a few that are extremely visible to decision makers in my company, and I want to find a way to quickly search the entire report for broken or errored formulas or cell values (#REF, #DIV/0!, etc.) before sending out the report. Some of the files are 10-18 MB, and I am trying to find the most efficient way to find any such errors. I could loop through every cell in the used range of each sheet of each report, but that would take forever. Is there a better way to accomplish this task in VBA that I have somehow been missing? I'm picturing a function that can be used to determine if there are errors anywhere in the report, and then I could use the result (true/false) to send an error notice email. Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
Maybe something like

Sub cjindenver()
   Dim Ws As Worksheet
   Dim Chk As Range
   
   For Each Ws In Worksheets
      On Error Resume Next
      Set Chk = Ws.UsedRange.SpecialCells(xlFormulas, xlErrors)
      On Error GoTo 0
      If Not Chk Is Nothing Then MsgBox "errors on " & Ws.Name
      Set Chk = Nothing
   Next Ws
End Sub

 

[sadboy309]

You click (Ctrl+G) > Special.. > Select Formulas + click check in for Errors > click OK.

 

[cjindenver]

Hi & welcome to MrExcel.
Maybe something like

Sub cjindenver()
   Dim Ws As Worksheet
   Dim Chk As Range
   
   For Each Ws In Worksheets
      On Error Resume Next
      Set Chk = Ws.UsedRange.SpecialCells(xlFormulas, xlErrors)
      On Error GoTo 0
      If Not Chk Is Nothing Then MsgBox "errors on " & Ws.Name
      Set Chk = Nothing
   Next Ws
End Sub

That seems to be doing what I want, and I think I can tweak this a bit to do exactly what I'm looking for. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback