How to find list of all inconsistent Excel formulas RRS feed

  • Question

  • Hi,

    I'm using Interop.Excel API and C#.

    Please suggest how to find all inconsistent formulas in Excel?

    Thursday, August 10, 2017 6:29 AM

All replies

  • Please suggest how to find all inconsistent formulas in Excel?

    What do you mean by "inconsistent formulas"?


    Thursday, August 10, 2017 10:07 AM
  • I meant formulas which are not correct, have some reference error, etc.
    Thursday, August 10, 2017 11:01 AM
  • have some reference error

    You have to call Cells.SpecialCells(XlCellType.xlCellTypeFormulas, XlSpecialCellsValue.xlErrors) to get all cells with errors.

    If you want to find a special error, you have to check the value of each found cell if it match an error.

    Unfortunately I can't give you an example in C#, the code below works in VBA.


    Sub Test()
      Dim Ws As Worksheet
      Dim Where As Range, This As Range
      Dim Value As Variant
      'Refer to the active sheet
      Set Ws = ActiveSheet
      'Errors off, an error is raised if no cells are found
      On Error Resume Next
      'Find cells with error formulas
      Set Where = Ws.Cells.SpecialCells(XlCellType.xlCellTypeFormulas, XlSpecialCellsValue.xlErrors)
      'Check each cell
      For Each This In Where
        'Get the value
        Value = This.Value
        'Do we have a REF error?
        If Value = CVErr(xlErrRef) Then
          'Show it
          Debug.Print "#REF error in " & This.Address
        End If
    End Sub

    Thursday, August 10, 2017 1:17 PM
  • Hi Sahrabh,

    I agree with Andreas, SpecialCells is a nice idea.

    Here is the C# code I convert from Andreas's code.

                Excel.Worksheet worksheet = xlApp.ActiveWorkbook.ActiveSheet;
                Excel.Range range;
                    range = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeFormulas,Excel.XlSpecialCellsValue.xlErrors);
                    //select all the cells with error formula
                catch (Exception ex)
                    System.Windows.Forms.MessageBox.Show("There is no cell with error formula");

    Best Regards,


    Friday, August 11, 2017 7:38 AM