none
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"?

    Andreas.

    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.

    Andreas.

    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
      Next
    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;
                try
                {
                    range = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeFormulas,Excel.XlSpecialCellsValue.xlErrors);
                    //select all the cells with error formula
                    range.Select();
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show("There is no cell with error formula");
                }

    Best Regards,

    Terry

    Friday, August 11, 2017 7:38 AM