Pretty slick. Thanks.
A few things:
I'm getting the hourglass hanging after running it.The only way to get rid
of it is to close the box or ESC.
Can the results be printed or copied to a section of a worksheet ?
It evalutas hidden sheets also. Is there a way to have it only look for
visible sheets ?
Is that a text reference to the "#ref!", meaning it's not only producing
results for the evaluated #Ref! but even if there happens to be #REF! as
text, correct ? Just curious.

Thanks again,

Steve
"Rick Rothstein" wrote:

Keeping your output structure as is, couldn't you use this slightly simpler
For Each loop?

Sub Foo()
Dim WS As Worksheet, Res As String
Debug.Print String$(40, "-")
For Each WS In ActiveWindow.SelectedSheets
If Not WS.Cells.Find(What:="#REF!", LookIn:=xlValues) Is Nothing Then
Debug.Print WS.Name
Res = Res & vbLf & WS.Name
End If
Next
Debug.Print String$(40, "-")
If Res = "" Then Res = "<none" Else Res = Mid$(Res, 2)
MsgBox Res, vbOKOnly, "Worksheets with #REF! errors"
End Sub

--
Rick (MVP - Excel)



"Harlan Grove" wrote in message
...
Steve wrote...
...
a separate sheet that would show which sheets have #REF!'s., something
like:

Sheet a! ok
Sheet b! Ref
Sheet c! Ref
Sheet d! ok
...

If you want to identify worksheets which have cells evaluating to
#REF!, then with the worksheet name in cell A3, you could use the
formula

=IF(COUNTIF(INDIRECT("'"&A3&"'!A:IV"),"=#REF!"),"R ef","ok")

Note that such formulas recalculate very slowly. Since you wouldn't
need to do this often, you may be better off using a macro to list the
worksheets with cells evaluating to #REF!. Something like


Sub foo()
Dim ws As Worksheet, wsc As Sheets, res As String

If ActiveWindow.SelectedSheets.Count 1 Then
Set wsc = ActiveWindow.SelectedSheets
Else
Set wsc = ActiveWindow.Parent.Worksheets
End If

On Error Resume Next
Debug.Print String$(40, "-")

For Each ws In wsc
Call ws.UsedRange _
.SpecialCells(xlCellTypeFormulas, xlErrors) _
.Find(what:="#REF!", LookIn:=xlValues)

If Err.Number = 0 Then
res = res & vbLf & ws.Name
Debug.Print ws.Name
Else
Err.Clear
End If
Next ws

On Error GoTo 0
Debug.Print String$(40, "-")

If res = "" Then res = "<none" Else res = Mid$(res, 2)
MsgBox Title:="Worksheets with #REF! errors", Prompt:=res,
Buttons:=vbOKOnly

End Sub

.