A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Id'ing tabs that contain #REF!'s



 
 
Thread Tools Display Modes
  #11  
Old April 21st, 2010, 05:01 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Id'ing tabs that contain #REF!'s

The address you should have sent it to was (minus the line feeds and
converting AT to @)...

rick.news
AT
verizon.net

Is that what you tried? I am getting email at that address, so it is
working. Try it again.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
I removed the No.SPAM in front of and after the @ but it got kicked back
as
undeliverable.

"Rick Rothstein" wrote:

I'm not sure what to tell you about finding #REF! errors on sheets that
do
not have them... I cannot duplicate that result here. If you want to send
your workbook to me so that I can see it happen, then maybe I'll be able
to
figure out why. If you choose to do this, just remove the NO.SPAM stuff
from
my listed email address.

As for making the list print out in multiple cells, do the following...

Change the Dim statement to this...

Dim WS As Worksheet, Res As String, TempArray() As String

Then replace the last line of code with these lines...

TempArray = Split(Res, vbLf)
With Worksheets("Sheet1")
.Columns(1).Clear
.Range("A1").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

--
Rick (MVP - Excel)



"Steve" wrote in message
...
Thank you very much for the very thorough explanations.
I just had those sheets hidden, but your suggested fix now looks at
only
the
visible sheets.

Two last things:
Is there a way to put the list starting in A1 thru, e.g. A20. This
currently
is putting the entire list in just A1, causing Row 1 to be very, very
tall.
Also, it seems to be listing sheets that do not have #REF! in them. I
did
searches for them thinking it had #REF!s, but the search resulted in
no
#REF!s being found.
I also double-checked via this from Cal =COUNTIF(Sheet2!A1:Z400,#REF!)
and
that also is showing no #REF!s.

Thanks again,

Steve
"Rick Rothstein" wrote:

1) The hour glass cursor is what Excel does while it is displaying a
MessageBox... I'm pretty sure you can't turn it off except to dismiss
the
MessageBox.

2) You can output the contents of the Res variable anywhere you need
it.
Harlan used a MessageBox and I decided to use his same output
structure
in
my code, hence, I also used a MessageBox. To put it in a cell, say for
example purposes on Sheet1 in Cell A1, just change the last line of
code
to
this...

Worksheets("Sheet1").Range("A1").Value = Res

3) As the code was structured, it looked as Selected Sheets only...
how
did
you select a hidden sheet? I guess if you are running this through a
loop
of
all sheets, you could change this line in the If...Then statement...

WS.Cells.Find(What:="#REF!", LookIn:=xlValues)

to this...

WS.Cells.SpecialCells(xlCellTypeVisible).Find(What :="#REF!",LookIn:=xlValues)

4) Yes, depending on what your last Find settings were, it *might*
find
#REF!, #Ref!, #ref!, etc. as a text string within cells displaying
text
containing those characters. I say "might" because Find has a
"feature"
whereby it remembers the settings previously assigned to its
arguments.
So,
if you had previously set the LookAt argument to xlPart, then it would
find
the text #REF! within a larger piece of text containing it. Whether if
would
find the other cases (#Ref!, #ref!, etc.) would depend on the previous
setting for the MatchCase argument. I assumed that such text would not
appear in your cell's normal text strings, so I didn't account for it.
We
could control it somewhat be changing my Find function reference in
the
If..Then statement to this...

Find(What:="#REF!", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=True)

This would still find "#REF!" as a text string IF your user decided to
type
that into a cell or if you had any formulas that returned that exact
text
string (which, personally, I think would be a silly thing to do).
However,
if even this is a possibility, then you would need to go with Harlan's
code
as he restricts his search to only those cells that returned an error.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
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

.

.

.

  #12  
Old April 21st, 2010, 06:48 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Id'ing tabs that contain #REF!'s

Steve followed up with me via email and, to keep the archive complete, here
is the response I sent back to him...

The code seems to work as designed. The code, as written, is meant to be
called AFTER you select all the worksheets you want to apply it to (this
goes back to my follow up question asking how you were selecting your hidden
worksheets... you can't as far as I know). To select the worksheets, click
on the first one and then Shift-Click on the last one... you will see all of
the sheet tabs become the "selected color" indicating they are selected...
running the code now produces the list you wanted. I'm now guessing that you
don't want the macro to operate like this; rather, you just **always** want
to process **all** visible sheets. See if this modified macro does what you
want...

Sub Findrefs()
'
' Findrefs Macro
' Macro recorded 4/21/2010 by Steve

Dim WS As Worksheet, Res As String, TempArray() 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
If WS.Visible Then
Call WS.UsedRange.Find(What:="#REF!", LookIn:=xlValues)
If Err.Number = 0 Then
Res = Res & vbLf & WS.Name
Debug.Print WS.Name
Else
Err.Clear
End If
End If
Next WS
On Error GoTo 0
Debug.Print String$(40, "-")
If Res = "" Then Res = "none" Else Res = Mid$(Res, 2)
TempArray = Split(Res, vbLf)
With Worksheets("Refsheet")
.Columns(1).Clear
.Range("A3").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
I'm not sure what to tell you about finding #REF! errors on sheets that do
not have them... I cannot duplicate that result here. If you want to send
your workbook to me so that I can see it happen, then maybe I'll be able
to figure out why. If you choose to do this, just remove the NO.SPAM stuff
from my listed email address.

As for making the list print out in multiple cells, do the following...

Change the Dim statement to this...

Dim WS As Worksheet, Res As String, TempArray() As String

Then replace the last line of code with these lines...

TempArray = Split(Res, vbLf)
With Worksheets("Sheet1")
.Columns(1).Clear
.Range("A1").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

--
Rick (MVP - Excel)



"Steve" wrote in message
...
Thank you very much for the very thorough explanations.
I just had those sheets hidden, but your suggested fix now looks at only
the
visible sheets.

Two last things:
Is there a way to put the list starting in A1 thru, e.g. A20. This
currently
is putting the entire list in just A1, causing Row 1 to be very, very
tall.
Also, it seems to be listing sheets that do not have #REF! in them. I did
searches for them thinking it had #REF!s, but the search resulted in no
#REF!s being found.
I also double-checked via this from Cal =COUNTIF(Sheet2!A1:Z400,#REF!)
and
that also is showing no #REF!s.

Thanks again,

Steve
"Rick Rothstein" wrote:

1) The hour glass cursor is what Excel does while it is displaying a
MessageBox... I'm pretty sure you can't turn it off except to dismiss
the
MessageBox.

2) You can output the contents of the Res variable anywhere you need it.
Harlan used a MessageBox and I decided to use his same output structure
in
my code, hence, I also used a MessageBox. To put it in a cell, say for
example purposes on Sheet1 in Cell A1, just change the last line of code
to
this...

Worksheets("Sheet1").Range("A1").Value = Res

3) As the code was structured, it looked as Selected Sheets only... how
did
you select a hidden sheet? I guess if you are running this through a
loop of
all sheets, you could change this line in the If...Then statement...

WS.Cells.Find(What:="#REF!", LookIn:=xlValues)

to this...

WS.Cells.SpecialCells(xlCellTypeVisible).Find(What :="#REF!",LookIn:=xlValues)

4) Yes, depending on what your last Find settings were, it *might* find
#REF!, #Ref!, #ref!, etc. as a text string within cells displaying text
containing those characters. I say "might" because Find has a "feature"
whereby it remembers the settings previously assigned to its arguments.
So,
if you had previously set the LookAt argument to xlPart, then it would
find
the text #REF! within a larger piece of text containing it. Whether if
would
find the other cases (#Ref!, #ref!, etc.) would depend on the previous
setting for the MatchCase argument. I assumed that such text would not
appear in your cell's normal text strings, so I didn't account for it.
We
could control it somewhat be changing my Find function reference in the
If..Then statement to this...

Find(What:="#REF!", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

This would still find "#REF!" as a text string IF your user decided to
type
that into a cell or if you had any formulas that returned that exact
text
string (which, personally, I think would be a silly thing to do).
However,
if even this is a possibility, then you would need to go with Harlan's
code
as he restricts his search to only those cells that returned an error.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
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

.

.

  #13  
Old April 21st, 2010, 07:11 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein[_2_]
external usenet poster
 
Posts: 2,013
Default Id'ing tabs that contain #REF!'s

****Code Correction****

I just sent this to Steve...

The code I sent you just a little while ago does not seem to work correctly;
try this macro instead...

Sub Findrefs()
'
' Findrefs Macro
' Macro recorded 4/21/2010 by Steve
'
Dim WS As Worksheet, Res As String, TempArray() As String
On Error Resume Next
Debug.Print String$(40, "-")
For Each WS In ActiveWindow.Parent.Worksheets
If WS.Visible Then
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
End If
Err.Clear
End If
Next WS
On Error GoTo 0
Debug.Print String$(40, "-")
If Res = "" Then Res = "none" Else Res = Mid$(Res, 2)
TempArray = Split(Res, vbLf)
With Worksheets("Refsheet")
.Columns(1).Clear
.Range("A3").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Steve followed up with me via email and, to keep the archive complete,
here is the response I sent back to him...

The code seems to work as designed. The code, as written, is meant to be
called AFTER you select all the worksheets you want to apply it to (this
goes back to my follow up question asking how you were selecting your
hidden worksheets... you can't as far as I know). To select the
worksheets, click on the first one and then Shift-Click on the last one...
you will see all of the sheet tabs become the "selected color" indicating
they are selected... running the code now produces the list you wanted.
I'm now guessing that you don't want the macro to operate like this;
rather, you just **always** want to process **all** visible sheets. See if
this modified macro does what you want...

Sub Findrefs()
'
' Findrefs Macro
' Macro recorded 4/21/2010 by Steve

Dim WS As Worksheet, Res As String, TempArray() 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
If WS.Visible Then
Call WS.UsedRange.Find(What:="#REF!", LookIn:=xlValues)
If Err.Number = 0 Then
Res = Res & vbLf & WS.Name
Debug.Print WS.Name
Else
Err.Clear
End If
End If
Next WS
On Error GoTo 0
Debug.Print String$(40, "-")
If Res = "" Then Res = "none" Else Res = Mid$(Res, 2)
TempArray = Split(Res, vbLf)
With Worksheets("Refsheet")
.Columns(1).Clear
.Range("A3").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
I'm not sure what to tell you about finding #REF! errors on sheets that
do not have them... I cannot duplicate that result here. If you want to
send your workbook to me so that I can see it happen, then maybe I'll be
able to figure out why. If you choose to do this, just remove the NO.SPAM
stuff from my listed email address.

As for making the list print out in multiple cells, do the following...

Change the Dim statement to this...

Dim WS As Worksheet, Res As String, TempArray() As String

Then replace the last line of code with these lines...

TempArray = Split(Res, vbLf)
With Worksheets("Sheet1")
.Columns(1).Clear
.Range("A1").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

--
Rick (MVP - Excel)



"Steve" wrote in message
...
Thank you very much for the very thorough explanations.
I just had those sheets hidden, but your suggested fix now looks at only
the
visible sheets.

Two last things:
Is there a way to put the list starting in A1 thru, e.g. A20. This
currently
is putting the entire list in just A1, causing Row 1 to be very, very
tall.
Also, it seems to be listing sheets that do not have #REF! in them. I
did
searches for them thinking it had #REF!s, but the search resulted in no
#REF!s being found.
I also double-checked via this from Cal =COUNTIF(Sheet2!A1:Z400,#REF!)
and
that also is showing no #REF!s.

Thanks again,

Steve
"Rick Rothstein" wrote:

1) The hour glass cursor is what Excel does while it is displaying a
MessageBox... I'm pretty sure you can't turn it off except to dismiss
the
MessageBox.

2) You can output the contents of the Res variable anywhere you need
it.
Harlan used a MessageBox and I decided to use his same output structure
in
my code, hence, I also used a MessageBox. To put it in a cell, say for
example purposes on Sheet1 in Cell A1, just change the last line of
code to
this...

Worksheets("Sheet1").Range("A1").Value = Res

3) As the code was structured, it looked as Selected Sheets only... how
did
you select a hidden sheet? I guess if you are running this through a
loop of
all sheets, you could change this line in the If...Then statement...

WS.Cells.Find(What:="#REF!", LookIn:=xlValues)

to this...

WS.Cells.SpecialCells(xlCellTypeVisible).Find(What :="#REF!",LookIn:=xlValues)

4) Yes, depending on what your last Find settings were, it *might* find
#REF!, #Ref!, #ref!, etc. as a text string within cells displaying text
containing those characters. I say "might" because Find has a "feature"
whereby it remembers the settings previously assigned to its arguments.
So,
if you had previously set the LookAt argument to xlPart, then it would
find
the text #REF! within a larger piece of text containing it. Whether if
would
find the other cases (#Ref!, #ref!, etc.) would depend on the previous
setting for the MatchCase argument. I assumed that such text would not
appear in your cell's normal text strings, so I didn't account for it.
We
could control it somewhat be changing my Find function reference in the
If..Then statement to this...

Find(What:="#REF!", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

This would still find "#REF!" as a text string IF your user decided to
type
that into a cell or if you had any formulas that returned that exact
text
string (which, personally, I think would be a silly thing to do).
However,
if even this is a possibility, then you would need to go with Harlan's
code
as he restricts his search to only those cells that returned an error.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
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

.

.

  #14  
Old April 21st, 2010, 08:08 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default Id'ing tabs that contain #REF!'s

By just running it without selecting any WS's, it lists every worksheet in
the file.
By selecting all the WS's, it does list only a few, but it is overlisting
them, meaning, it lists WS's without the #REF! in them. It's probably my
file. It's over 19KB in size, and I've had weird things happen with it before
that don't seem to make sense, so this could be one of those situations. I do
appreciate all you efforts, like I said, it's probably the file.

Thanks again for all your patience,

Steve

"Rick Rothstein" wrote:

Steve followed up with me via email and, to keep the archive complete, here
is the response I sent back to him...

The code seems to work as designed. The code, as written, is meant to be
called AFTER you select all the worksheets you want to apply it to (this
goes back to my follow up question asking how you were selecting your hidden
worksheets... you can't as far as I know). To select the worksheets, click
on the first one and then Shift-Click on the last one... you will see all of
the sheet tabs become the "selected color" indicating they are selected...
running the code now produces the list you wanted. I'm now guessing that you
don't want the macro to operate like this; rather, you just **always** want
to process **all** visible sheets. See if this modified macro does what you
want...

Sub Findrefs()
'
' Findrefs Macro
' Macro recorded 4/21/2010 by Steve

Dim WS As Worksheet, Res As String, TempArray() 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
If WS.Visible Then
Call WS.UsedRange.Find(What:="#REF!", LookIn:=xlValues)
If Err.Number = 0 Then
Res = Res & vbLf & WS.Name
Debug.Print WS.Name
Else
Err.Clear
End If
End If
Next WS
On Error GoTo 0
Debug.Print String$(40, "-")
If Res = "" Then Res = "none" Else Res = Mid$(Res, 2)
TempArray = Split(Res, vbLf)
With Worksheets("Refsheet")
.Columns(1).Clear
.Range("A3").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
I'm not sure what to tell you about finding #REF! errors on sheets that do
not have them... I cannot duplicate that result here. If you want to send
your workbook to me so that I can see it happen, then maybe I'll be able
to figure out why. If you choose to do this, just remove the NO.SPAM stuff
from my listed email address.

As for making the list print out in multiple cells, do the following...

Change the Dim statement to this...

Dim WS As Worksheet, Res As String, TempArray() As String

Then replace the last line of code with these lines...

TempArray = Split(Res, vbLf)
With Worksheets("Sheet1")
.Columns(1).Clear
.Range("A1").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

--
Rick (MVP - Excel)



"Steve" wrote in message
...
Thank you very much for the very thorough explanations.
I just had those sheets hidden, but your suggested fix now looks at only
the
visible sheets.

Two last things:
Is there a way to put the list starting in A1 thru, e.g. A20. This
currently
is putting the entire list in just A1, causing Row 1 to be very, very
tall.
Also, it seems to be listing sheets that do not have #REF! in them. I did
searches for them thinking it had #REF!s, but the search resulted in no
#REF!s being found.
I also double-checked via this from Cal =COUNTIF(Sheet2!A1:Z400,#REF!)
and
that also is showing no #REF!s.

Thanks again,

Steve
"Rick Rothstein" wrote:

1) The hour glass cursor is what Excel does while it is displaying a
MessageBox... I'm pretty sure you can't turn it off except to dismiss
the
MessageBox.

2) You can output the contents of the Res variable anywhere you need it.
Harlan used a MessageBox and I decided to use his same output structure
in
my code, hence, I also used a MessageBox. To put it in a cell, say for
example purposes on Sheet1 in Cell A1, just change the last line of code
to
this...

Worksheets("Sheet1").Range("A1").Value = Res

3) As the code was structured, it looked as Selected Sheets only... how
did
you select a hidden sheet? I guess if you are running this through a
loop of
all sheets, you could change this line in the If...Then statement...

WS.Cells.Find(What:="#REF!", LookIn:=xlValues)

to this...

WS.Cells.SpecialCells(xlCellTypeVisible).Find(What :="#REF!",LookIn:=xlValues)

4) Yes, depending on what your last Find settings were, it *might* find
#REF!, #Ref!, #ref!, etc. as a text string within cells displaying text
containing those characters. I say "might" because Find has a "feature"
whereby it remembers the settings previously assigned to its arguments.
So,
if you had previously set the LookAt argument to xlPart, then it would
find
the text #REF! within a larger piece of text containing it. Whether if
would
find the other cases (#Ref!, #ref!, etc.) would depend on the previous
setting for the MatchCase argument. I assumed that such text would not
appear in your cell's normal text strings, so I didn't account for it.
We
could control it somewhat be changing my Find function reference in the
If..Then statement to this...

Find(What:="#REF!", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

This would still find "#REF!" as a text string IF your user decided to
type
that into a cell or if you had any formulas that returned that exact
text
string (which, personally, I think would be a silly thing to do).
However,
if even this is a possibility, then you would need to go with Harlan's
code
as he restricts his search to only those cells that returned an error.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
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

.

.

.

  #15  
Old April 21st, 2010, 09:00 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 2,662
Default Id'ing tabs that contain #REF!'s

Getting VERY close.
But in addition to the #REF!s it's also listing sheets that have #N/A.




"Rick Rothstein" wrote:

****Code Correction****

I just sent this to Steve...

The code I sent you just a little while ago does not seem to work correctly;
try this macro instead...

Sub Findrefs()
'
' Findrefs Macro
' Macro recorded 4/21/2010 by Steve
'
Dim WS As Worksheet, Res As String, TempArray() As String
On Error Resume Next
Debug.Print String$(40, "-")
For Each WS In ActiveWindow.Parent.Worksheets
If WS.Visible Then
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
End If
Err.Clear
End If
Next WS
On Error GoTo 0
Debug.Print String$(40, "-")
If Res = "" Then Res = "none" Else Res = Mid$(Res, 2)
TempArray = Split(Res, vbLf)
With Worksheets("Refsheet")
.Columns(1).Clear
.Range("A3").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Steve followed up with me via email and, to keep the archive complete,
here is the response I sent back to him...

The code seems to work as designed. The code, as written, is meant to be
called AFTER you select all the worksheets you want to apply it to (this
goes back to my follow up question asking how you were selecting your
hidden worksheets... you can't as far as I know). To select the
worksheets, click on the first one and then Shift-Click on the last one...
you will see all of the sheet tabs become the "selected color" indicating
they are selected... running the code now produces the list you wanted.
I'm now guessing that you don't want the macro to operate like this;
rather, you just **always** want to process **all** visible sheets. See if
this modified macro does what you want...

Sub Findrefs()
'
' Findrefs Macro
' Macro recorded 4/21/2010 by Steve

Dim WS As Worksheet, Res As String, TempArray() 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
If WS.Visible Then
Call WS.UsedRange.Find(What:="#REF!", LookIn:=xlValues)
If Err.Number = 0 Then
Res = Res & vbLf & WS.Name
Debug.Print WS.Name
Else
Err.Clear
End If
End If
Next WS
On Error GoTo 0
Debug.Print String$(40, "-")
If Res = "" Then Res = "none" Else Res = Mid$(Res, 2)
TempArray = Split(Res, vbLf)
With Worksheets("Refsheet")
.Columns(1).Clear
.Range("A3").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
I'm not sure what to tell you about finding #REF! errors on sheets that
do not have them... I cannot duplicate that result here. If you want to
send your workbook to me so that I can see it happen, then maybe I'll be
able to figure out why. If you choose to do this, just remove the NO.SPAM
stuff from my listed email address.

As for making the list print out in multiple cells, do the following...

Change the Dim statement to this...

Dim WS As Worksheet, Res As String, TempArray() As String

Then replace the last line of code with these lines...

TempArray = Split(Res, vbLf)
With Worksheets("Sheet1")
.Columns(1).Clear
.Range("A1").Resize(1 + UBound(TempArray)) = _
WorksheetFunction.Transpose(TempArray)
End With

--
Rick (MVP - Excel)



"Steve" wrote in message
...
Thank you very much for the very thorough explanations.
I just had those sheets hidden, but your suggested fix now looks at only
the
visible sheets.

Two last things:
Is there a way to put the list starting in A1 thru, e.g. A20. This
currently
is putting the entire list in just A1, causing Row 1 to be very, very
tall.
Also, it seems to be listing sheets that do not have #REF! in them. I
did
searches for them thinking it had #REF!s, but the search resulted in no
#REF!s being found.
I also double-checked via this from Cal =COUNTIF(Sheet2!A1:Z400,#REF!)
and
that also is showing no #REF!s.

Thanks again,

Steve
"Rick Rothstein" wrote:

1) The hour glass cursor is what Excel does while it is displaying a
MessageBox... I'm pretty sure you can't turn it off except to dismiss
the
MessageBox.

2) You can output the contents of the Res variable anywhere you need
it.
Harlan used a MessageBox and I decided to use his same output structure
in
my code, hence, I also used a MessageBox. To put it in a cell, say for
example purposes on Sheet1 in Cell A1, just change the last line of
code to
this...

Worksheets("Sheet1").Range("A1").Value = Res

3) As the code was structured, it looked as Selected Sheets only... how
did
you select a hidden sheet? I guess if you are running this through a
loop of
all sheets, you could change this line in the If...Then statement...

WS.Cells.Find(What:="#REF!", LookIn:=xlValues)

to this...

WS.Cells.SpecialCells(xlCellTypeVisible).Find(What :="#REF!",LookIn:=xlValues)

4) Yes, depending on what your last Find settings were, it *might* find
#REF!, #Ref!, #ref!, etc. as a text string within cells displaying text
containing those characters. I say "might" because Find has a "feature"
whereby it remembers the settings previously assigned to its arguments.
So,
if you had previously set the LookAt argument to xlPart, then it would
find
the text #REF! within a larger piece of text containing it. Whether if
would
find the other cases (#Ref!, #ref!, etc.) would depend on the previous
setting for the MatchCase argument. I assumed that such text would not
appear in your cell's normal text strings, so I didn't account for it.
We
could control it somewhat be changing my Find function reference in the
If..Then statement to this...

Find(What:="#REF!", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

This would still find "#REF!" as a text string IF your user decided to
type
that into a cell or if you had any formulas that returned that exact
text
string (which, personally, I think would be a silly thing to do).
However,
if even this is a possibility, then you would need to go with Harlan's
code
as he restricts his search to only those cells that returned an error.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.