Detect Errors in Excel File

Hello there!

I would like to build a bot that checks whether formula errors (i.e. ref!, #DIV/0!, name?, value! etc) exist in the excel sheet. This includes checking in visible and hidden tabs. If yes I would like to get the specific cell address.

Any advice or good idea on what’s the best way to do this? :thinking:
If it is a macro, how should the script look like?

Hi @WS_Chai ,
In Excel file, you can read range to get data table
For each row you can check value in here with correct value by formula
Compare between two value
Regards,
LNV

  1. Read the Excel file: Use the “Excel Application Scope” activity to read the Excel file. Make sure to set the “Visible” property to False to handle hidden sheets as well.
  2. Iterate through each cell: Use the “Read Cell Formula” activity inside a nested “For Each Row” loop to iterate through all cells in all sheets.
  3. Check for formula errors: Within the loop, check if the formula in the cell starts with “#”. If it does, add the cell address to a list of errors.
  4. Output the results: After processing all sheets, display or save the cell addresses with formula errors.

Note:-

  • If the formula starts with “#”, add the cell address to a list variable (e.g., errorCells).You can use the row and column variables along with the CurrentIndex property of the “For Each” activities to get the cell address (e.g., row.Index and column.ColumnName).
  • After processing all cells in all sheets, use a message box or write the contents of the errorCells list to a file to display or save the cell addresses with formula errors.

Hi @WS_Chai

Check the below thread, there is a below vba code to get the formula errors.

Hope it helps!!