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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Id'ing tabs that contain #REF!'s
I have a workbook with quite a few sheets, many having formulas that refer to
other sheets. Sometimes when I make changes to some of the cells in certain sheets, I'll get #REF!'s on other sheets/cells. I'm thinking I'd like to have 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 So I could easily goto that sheet and fix the #REF!. Is that possible ? Thanks, Steve |
#2
|
|||
|
|||
Id'ing tabs that contain #REF!'s
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 |
#3
|
|||
|
|||
Id'ing tabs that contain #REF!'s
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 |
#4
|
|||
|
|||
Id'ing tabs that contain #REF!'s
I can't get the countif(indirect to work. It's producing a zero.
The macro is working, but it seems to only identify whether ref or not on the sheet I'm on. I guess I have to go to each sheet and run the macro ? Thanks, Steve "Harlan Grove" wrote: 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 . |
#5
|
|||
|
|||
Id'ing tabs that contain #REF!'s
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 . |
#6
|
|||
|
|||
Id'ing tabs that contain #REF!'s
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 . |
#7
|
|||
|
|||
Id'ing tabs that contain #REF!'s
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 . . |
#8
|
|||
|
|||
Id'ing tabs that contain #REF!'s
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 . . |
#9
|
|||
|
|||
Id'ing tabs that contain #REF!'s
Thanks. I just sent it to you.
Nicely putting names in the entire A column now, but still all sheets, not just the #REF! sheets. Thanks again, Steve "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 . . . |
#10
|
|||
|
|||
Id'ing tabs that contain #REF!'s
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 . . . |
|
Thread Tools | |
Display Modes | |
|
|