Finding Invalid References

Roger H

New Member
Joined
Jun 27, 2008
Messages
4
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top