Display all errors

[pywhacket]

Hello,

I need help please. I have a large worksheet that has numerous formulas. There are a nmber of people working in it so when the data is manipulated errors occur, Is there a way to display all of the errors (in cells and formulas) in another tab?

Thanks!

 

[Jerry Sullivan]

Hi, What kind of errors are you wanting to be reported?

You would need criteria to define what constitutes an error in order to have a system of reporting it on another sheet.

For example Excel shows a Warning Flag in the corner of a cell when the formula isn't consistent with the ones on either side...but that doesn't necessarily mean that formula is in error.

You might want to consider protecting the worksheet and locking cells that you do not want the other users to change formatting or formulas...that would probably be much simpler.

 

[pywhacket]

Hello Jerry,

I wish I could lock the spreedsheat, however, it isn't not mine. I am looking for #ref errors. The information we are addind requires adding and deleting rows and the inevitably causes some #ref errors. I am trying to help others repair theirs and when there are 70,000 plus lines it is a lot to search through. I was hoping I could "export" a list of the errors to another sheet to know exactly what is there.

Thanks,

Michele

 

[Sektor]

Alas, you will never discover what #REF means. See it yourself in sheet.xml file - there's no clue what is this reference.

 

[pywhacket]

I would like to see where they are located in a list.

 

[Sektor]

Sub FormulaErrors()

    Dim cell As Range, j As Long
    Dim arr() As Variant
    
    j = 1
    For Each cell In ActiveSheet.UsedRange
        If InStr(cell.Formula, "#REF") > 0 Then
            ReDim Preserve arr(1 To j)
            arr(j) = cell.Address
            j = j + 1
        End If
    Next

    Sheets.Add.Range("A1").Resize(UBound(arr), 1) = WorksheetFunction.Transpose(arr)

End Sub

 

[pywhacket]

Thank you! That helps a lot!

 

[xenou]

Also just:

1) Control + F (to open the Find Dialog)
2) Find #REF (search in values)
3) Find All

When the dialog displays the results you also can click in the results to go right to the cell.