Excel VBA – Error Scan

From time to time, I need to work on more advanced Excel projects.  Often, I am tasked with evaluating existing Excel files, many of which utilize extensive formulas.  In many cases errors appear on the spreadsheet and are difficult to detect.

Excel VBA – Error Scan

I use the following macro to quickly find the actual cell that is causing the error.  This is a very simple setup, but has worked very well when I need to quickly find formulas or cells that have any types of error messages returned.

The actual VBA code is quite simple:

For simplicity, say you have the following table in Excel:

 

Excel VBA - Error Scan

 

Simply highlight the range and run the macro “ErrorScan”

 

Excel VBA - Error Scan

 

The macro will detect the error (in cell D5), then stop the macro and select the cell with the first detected error:

 

Excel VBA - Error Scan

 

This comes in handy on large spreadsheets, where the actual error message does not jump out.  I like to build this into many of my projects as I am writing and developing the VBA code.  I call this macro as I build others, to check for errors that I do not consider looking for.  You can modify this macro to meet your needs.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment