Results 1 to 3 of 3

Thread: How to find Invalid Reference in Excel

  1. #1
    Join Date
    Dec 2007

    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.

  2. #2
    Join Date
    Jan 2008

    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

    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"
            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

  3. #3
    Join Date
    Jan 2011

    idea 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.

Similar Threads

  1. Replies: 4
    Last Post: 14-05-2012, 09:10 PM
  2. Replies: 2
    Last Post: 25-02-2012, 09:51 AM
  3. Replies: 2
    Last Post: 21-02-2012, 11:59 AM
  4. New virus Defense Center, can't find any reference on google
    By Narmad in forum Networking & Security
    Replies: 5
    Last Post: 20-07-2010, 03:36 PM
  5. Cell Reference in Excel
    By kamina23 in forum Windows Software
    Replies: 4
    Last Post: 07-10-2009, 11:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts