How to find Invalid Reference in Excel

[Ameya]

Sponsored Links

Hi friends,

I am using Excel 2003 and many a times i am getting the following error message as "A formula in this worksheet contains one or more invalid references" and it takes a very long time to find out those references is there any any way where i can easily find out Invalid Reference in my excel sheet.

[Sam.D]

I was also getting same issue since i have deleted some of the formula from my Excel sheet, see if that you have also made same mistake or else you can use this code too

Sub form_errors()
Dim s1 As String: s1 = ActiveSheet.Name
Dim rng, cell As Range
Set rng = Sheets(s1).UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
Application.DisplayAlerts = False
Select Case rng Is Nothing
    Case False
        On Error Resume Next
        Sheets(s1 & "_Invalid").Delete
        On Error GoTo 0
        Sheets.Add After:=Sheets(s1)
        ActiveSheet.Name = s1 & "_Invalid"
        Sheets(s1).Select
        For Each cell In rng
            Sheets(s1 & "_Invalid").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = cell.Address
            Sheets(s1 & "_Invalid").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = Replace(cell.Formula, "=", "'")
            Sheets(s1 & "_Invalid").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = cell.Value
        Next cell
End Select
Application.DisplayAlerts = True
End Sub

[shunnicu]

I just spent four hours trying to fix the same error in a work book with 36 work sheets. I finally fixed it by selecting all worksheets in the workbook and copying them to a new workbook. I had to hit "OK" 36 times before, but all the errors went away after this. Try it.