通过VBA在Excel中查找不一致的公式 [英] Find inconsistent formulas in Excel through VBA

问题描述

Excel检测公式是否与表中计算列不一致，并显示为一个绿色三角形（表示该单元格与列公式不一致）。如何通过VBA找到它们。我发现这个代码应该是诀窍（从 http：// www。 ozgrid.com/forum/showthread.php?t=145306 ），但这只适用于使用范围是正常范围而不是表格：

Excel detects if a formula is inconsistent with a calculated column in a table and shows them with a little green triangle (which says "This cell is inconsistent with the column formula"). How can I find them through VBA. I found this code that is supposed to do the trick (from http://www.ozgrid.com/forum/showthread.php?t=145306) but this only works when the used range is a normal range instead of a table:

Dim oneCell As Range 

For Each oneCell In ActiveSheet.UsedRange 
    If oneCell.Errors(xlInconsistentFormula).Value Then 
        oneCell.Interior.ColorIndex = 6 
    Else 
        oneCell.Interior.ColorIndex = xlNone 
    End If 
Next oneCell 

但是 oneCell.Errors（xlInconsistentFormula）.Value 只发送False，因此它不起作用。

But oneCell.Errors(xlInconsistentFormula).Value only sends "False" so it doesn't work.

这可以修改为在表中工作，而不是正常范围？

Can this be fixed to work in a table instead of a normal range?

编辑：xl不一致的模式不工作，如果你工作

xlInconsistentFormula doesn't do anything if you work in a table.

推荐答案

这样做：

Sub fhdjksjdfhs()
    Dim r As Range
    Dim rBig As Range
    Set rBig = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
    For Each r In rBig
        If r.Errors.Item(xlInconsistentFormula).Value = True Then
            r.Interior.ColorIndex = 6
        Else
            r.Interior.ColorIndex = xlNone
        End If
    Next r
End Sub

编辑＃1：

正如Kersijus 正确指出的那样，将一列或一组列转换为表可以抑制错误检查的级别，绿旗。此代码不会检测以这种方式被抑制的错误。

As Kersijus correctly points out, converting a column or set of columns into a Table suppresses the level of error-checking that raises the green flag. This code will not detect errors that are suppressed in this way.

这篇关于通过VBA在Excel中查找不一致的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！