Find all Errors in all sheets

[newapa]

Hi!

i'm trying to find Error #REF but i didn't manage it to work. and i don't know what is it i'm missing?

here the code

Dim ws As Worksheet
Dim R As Range
Dim sCell As Variant


For Each ws In ThisWorkbook.Worksheets
Set R = ws.UsedRange

For Each sCell In R
If sCell.Value = CVErr(xlErrRef) Then
sCell.Select
Stop:Resume
End If

Next sCell
Next ws


thanks in advance

 

[Rick Rothstein]

Okay, you showed us code that you say doesn't work, but you didn't tell us how you wanted it to work... exactly what do you want the code to do for you?

 

[Legacy 143009]

First, don't forget to Wrap your codes.

Try this,

Sub Test()
Dim ws As Worksheet
Dim R As Range, sCell As Range

For Each ws In ThisWorkbook.Worksheets
Set R = ws.UsedRange
For Each sCell In R
If IsError(sCell.Value) Then
sCell.Select
Exit Sub
End If
Next sCell
Next ws
End Sub

 

[newapa]

i wanted it to loop throuht all sheet and all cell
and find error #REF and i will change it manually.

 

[newapa]

Flashbond
hi i wated to fins error #REF only but u code will stop in alla errors.

i tryied this but i got runtime error '6' Overflow


Dim ws As Worksheet
Dim R As Range, sCell As Range

For Each ws In ThisWorkbook.Worksheets
Set R = ws.UsedRange

For Each sCell In R
If IsError(sCell.Value) Then

if scell.value = cverr(xlErrRef) then
sCell.Select Stop
end if

End If
Next sCell
Next ws

 

[Legacy 143009]

Please wrap your codes!

Paste this into your Workbook mocro, not sheet;

Sub Test()
Const RefText = "#REF"
Dim FindRef As Range
Dim SheetLoop
Dim FirstAddress
Dim UserOption
For SheetLoop = 1 To ThisWorkbook.Sheets.Count
Set FindRef = ThisWorkbook.Sheets(SheetLoop).Cells.Find(RefText, lookat:=xlPart, LookIn:=xlValues)

If Not FindRef Is Nothing Then
FirstAddress = FindRef.Address

While Not FindRef Is Nothing
UserOption = MsgBox("Sheet - " & ThisWorkbook.Sheets(SheetLoop).Name & ", cell " & FindRef.Address & vbNewLine & "Press OK to continue or cancel to exit", vbOKCancel)
If UserOption = vbCancel Then
Exit Sub
End If
Set FindRef = ThisWorkbook.Sheets(SheetLoop).Cells.FindNext(FindRef)
If FindRef.Address = FirstAddress Then
Set FindRef = Nothing
End If
Wend
End If
Next SheetLoop
End Sub

 

[Rick Rothstein]

i wanted it to loop throuht all sheet and all cell
and find error #REF and i will change it manually.

You do not need any VB code to do that... let Excel do all the hard work. Press CTRL+F to bring up the Find dialog box. Type #REF! into the "Find what" field. Click the "Options>>" button to reveal all the possible options and change the "Within" dropdown to "Workbook", change the "Look in" dropdown to "Formulas", and make sure both the "Match case" and the "Match entire cell contents" checkboxes are NOT checked, then click the "Find Next" button. You will be taken to a cell with a #REF! error in it... leave the Find dialog box on the screen and fix the cell as needed... once it is fixed, click the "Find Next" button again and fix the next #REF! error it takes you to... continue until the "Find Next" button cannot locate any more #REF! errors.

NOTE: The Find dialog box "remembers" its settings, so once you are through fixing all the cells with #REF! errors in them, change the "Within" dropdown back to "Worksheet" and change the "Look in" dropdown back to "Values" so they will be set to the expected defaults the next time you use the Find dialog box.