Detecting cells that have InconsistentFormula error

[DougVba]

I am trying to detect cells that have the InconsistentFormula error and if possible get each cell location.

After our administrator runs a setup macro, I want to remind her to look at any cells that have formula errors.

[Richard Buttrey]

On Thu, 30 Mar 2006 16:35:36 -0600, DougVba
<DougVba.25i80o_1143758402.9856@excelforum-nospam.com> wrote:

>
>::I am trying to detect cells that have the InconsistentFormula error
>and if possible get each cell location.
>
>After our administrator runs a setup macro, I want to remind her to
>look at any cells that have formula errors.::

In a macro

With ActiveSheet
Range(Range("A1"),
Range("A1").SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeFormulas,
16).Select

End With


Alternatively F5 (Goto), Special--> Formulas and select the Errors
checkbox, deselecting the other three.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[DougVba]

Richard, I attached the macro to a button for testing purposes.

When I ran the macro I received a dialog box:

Run-time error: '1004':
No cells were found.

I do have a formula error on the workbook.

[Richard Buttrey]

On Thu, 30 Mar 2006 17:52:26 -0600, DougVba
<DougVba.25ibhn_1143762902.54@excelforum-nospam.com> wrote:

>
>Richard, I attached the macro to a button for testing purposes.
>
>When I ran the macro I received a dialog box:
>
>Run-time error: '1004':
>No cells were found.
>
>I do have a formula error on the workbook.


Seems to work OK for me. Tested for #DIV/0!, #N/A, #VALUE!

What is in the cell where the error is, and what does the cell return?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[DougVba]

The cell is returning the error:
"The formula in this cell differs from the formulas in this area of the spreadsheet."

I was trying to duplicate our timesheet spreadsheet which is returning an "Inconsistent Formula" error, when adjacent Sum formulas differ in range by one cell.

It looks like the code that you gave to me should have caught the "differs" error as well.

Here is the code that I am using. Since I have not used this cabability before maybe I messed up someplace:

Sub FindIFerror()
If Application.ErrorCheckingOptions.InconsistentFormula Then

With ActiveSheet
Range(Range("A1"), Range("A1").SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeFormulas, 16).Select
End With

End If
End Sub

I am also wondering what exactly the "1004" error is telling me. I was assuming that it did not find an "error", but perhaps the range setup is incorrect.

Thanks for your help.

[DougVba]

Richard, just to add a little to the mix, I modified the code above slightly to test your examples. "Result" is defined as a variant.

With ActiveSheet
Result = Range(Range("A1"), Range("A1").SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeFormulas, 16).Select
If Result = True Then
MsgBox ("NASA, we have a problem!")
End If
End With

This works with a divide by zero error, but when I remove the divide by zero error, I return to the run time error.