How to find Invalid Reference in Excel
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.
Re: How to find Invalid Reference in Excel
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
Code:
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
Re: How to find Invalid Reference in Excel
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.