VBA code to search for #REF, #VALUE, #N/A within multiple excel sheets and produce report

Hello,

I am working with the excel workbook that has many functions/formulas build in. I was trying to build a VBA code that upon execution searches for all kinds of errors (#N/A, #Value, #REF) and produces the report with cell references. In one of the previous posts I fund this code that searches for #REF and was wondering whether it could be adopted to search for other errors. Any help would be greatly appreciated. Thanks in advance

Please Login or Register  to view this content.

Untested.

Please Login or Register  to view this content.

The code is failing with the type mismatch error on this line:

Please Login or Register  to view this content.

any idea as of why?

maybe something like this

Please Login or Register  to view this content.

This Code finds all applicable errors, however, it only looks through single tab. Also it does not dislay errors in a separate tab "errors" with the reference to the sheet and cell containing the error.

I've been playing with the code, but cannot get anywhere.

Any hlp will be greatly appreciated.

Please attach a sample with a desired result.

I got the code working by adding the array and dim around RefText. Attaching the code for those who might have the same issue.

Question: How do I make the code to display the error found in the cell after the cell reference? If there is an error in the sheet, I get the following:Sheet - Compare, Cell - $F$17. But I would like it to display something along the lines of : Sheet - Compare, Cell - $F$17, Error Type - #REF depending on the cell error. I am trying to modify this part:

Please Login or Register  to view this content.

Working Code for #REF, #VALUE, #N/A Error types

Please Login or Register  to view this content.