Results 1 to 4 of 4

Finding Invalid References

This is a discussion on Finding Invalid References within the Excel Questions forums, part of the Question Forums category; In Excel 2007 how can I most easily locate invalid references when the following message appears? "A formula in this ...

  1. #1
    New Member
    Join Date
    Jun 2008
    Posts
    4

    Default Finding Invalid References

    In Excel 2007 how can I most easily locate invalid references when the following message appears?

    "A formula in this worksheet contains one or more invalid references"

    Urgent help would be greatly appreciated

    Roger H

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: Finding Invalid References

    Roger H, not sure if you still need this answered or not... I'm not an 07 user but in theory this should find and list all formulas returning an error albeit not just ref's pnto a LOG sheet

    So assuming you're active on Sheet1 and ran this code a new sheet "Sheet1_Invalid" would be created and in columns A,B & C you would see cell reference, formula & result causing errors -- C showing error type.

    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
    Assuming 07 remains like 03 for Edit / Find you could look for #REF, looking in Values -- and opt to Find All -- this would list the results but to my knowledge you could not copy & paste the results for review.
    Does my a$$ look big in this picture ?

  3. #3
    New Member
    Join Date
    Jun 2008
    Posts
    4

    Default Re: Finding Invalid References

    Many thanks for the helpful code which I will hold for future reference

    By doing some googling I found that deleting charts can produce the invalid reference

    I also found a recommendation for a commercial product which removes invalid references ... I've used it previously and decided to get it again

    It worked instantly

    So while I am not wanting to name the product others may find it useful to know that there is a way to resolve this reported bug

    With Best Wishes and Thanks

    Roger H

  4. #4
    New Member
    Join Date
    Aug 2004
    Posts
    28

    Default Re: Finding Invalid References

    I am having the same problem and would love to find out about the product you talk about. It is only fair that you share with us what worked for you.

    Thank you!
    P

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com