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?
If it is a macro, how should the script look like?
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.
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.
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.
Output the results: After processing all sheets, display or save the cell addresses with formula errors.
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.