CODEWITHSUNDEEP
excelformula
Eric
Eric

Reputation: 367

Formula auditing - error checking for a specific region

I would like to ask whether it is possible to check inconsistent formula for a specific region.

In my case, col D and col E contain different sets of formula.

I just want to ensure that all formula in col E are consistent.

Is it possible to do so??

Upvotes: 0

Views: 1161

Answers (2)

Peter Albert
Peter Albert

Reputation: 17495

A quick and pragmatic way would be to use another column (temporarily): Assuming you want to check that each cell in column E has the formula =SUM(A1:D1), simply enter the formula =(SUM(A1:D1)-E1 in another column. Then select the column and filter for FALSE- this will give you all the formulas that have different results!

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

Here is one way.

Let's say your worksheet looks like this

enter image description here

Now Paste this code in a module and run it. It will tell you which cells have inconsistent formulas. See below screenshot

I have commented the code so that you will not have any problem understanding it. If you do simply ask :)

Option Explicit

Sub GetInConsCells()
    Dim ws As Worksheet
    Dim rng As Range, cl As Range, errCells As Range
    Dim ErrorCells As String
    Dim lRow As Long

    '~~> Create a temp copy of the sheet
    ThisWorkbook.Sheets("Sheet1").Copy After:=Sheets(ThisWorkbook.Sheets.Count)

    Set ws = ActiveSheet
    With ws
        '~~> Clear Col D and Col F Contents
        .Range("D:D,F:F").ClearContents

        '~~> Find the last row of col E
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        '~~> Set your range
        Set rng = Range("E1:E" & lRow)

        '~~> Check if the cells have inconsistent formulas
        For Each cl In rng
            If cl.Errors(xlInconsistentFormula).Value Then
                If errCells Is Nothing Then
                    Set errCells = cl
                Else
                    Set errCells = Union(cl, errCells)
                End If
            End If
        Next cl

        '~~> Display relevant message
        If Not errCells Is Nothing Then
            ErrorCells = errCells.Address
            MsgBox "Formulas in cells " & ErrorCells & " are inconsitent"
        Else
            MsgBox "All Formulas are consistent"
        End If
    End With

    '~~> Delete temp sheet
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
End Sub

enter image description here

Upvotes: 2

Related Questions