Find errors in Excel.

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

I have this code:

Sub t()
For Each s In ActiveWorkbook.Sheets
For Each c In s.UsedRange.Cells
If IsError(c) Then
errval = c.Value
If errval = CVErr(xlErrRef) Then
c.Interior.ColorIndex = 3
End If
End If
Next c
Next s
End Sub

It colors all cells red, if the contains the #REF! error. The problem is,
that my client would like it to select the first instans of the error and
stop. When he runs it a second time, it should stop with the second instans
and so on.

I don't see how this can be done?

Jan
 
Hi,

You could do this. The first time you encounter a REF error check if there
is a marker flag in a cell (I used the last column in the row). If there
isn't one then terminate the program and put an X (marker flag) in the last
row

Sub t()
For Each s In ActiveWorkbook.Sheets
For Each c In s.UsedRange.Cells
If IsError(c) Then
errval = c.Value
If errval = CVErr(xlErrRef) Then
If Cells(c.Row, Columns.Count) = "" Then
Cells(c.Row, Columns.Count) = "x"
c.Interior.ColorIndex = 3
c.Select
Exit Sub
End If
End If
End If
Next c
Next s
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Sorry flawed logic, it would miss 2 REF errors on the same row
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Have a go with something like the following

' code in a normal module
Private mRngLast As Range

Sub NextRefError()
Dim bGetNext As Boolean, bCompare As Boolean, bGotOld As Boolean
Dim i As Long, j As Long
Dim idx As Long
Dim firstAddress As String, sLast As String
Dim c As Range, rNextErr As Range
Dim ws As Worksheet

For i = 1 To 2
On Error Resume Next
idx = 0
idx = mRngLast.Parent.Index
On Error GoTo 0
If idx > 0 Then
If Not mRngLast.Parent.Parent Is ActiveWorkbook Then
If MsgBox("Do you want to switch to " & _
mRngLast.Parent.Parent.Name & _
vbCr & "Or press No to reset, then try again", _
vbYesNo) = vbYes Then
mRngLast.Parent.Activate
Else
Set mRngLast = Nothing
Exit Sub
End If
End If

bGotOld = True
bCompare = True

sLast = mRngLast.Address
Else
bCompare = False
idx = 1
End If

For j = idx To ActiveWorkbook.Worksheets.Count
With ActiveWorkbook.Worksheets(j)

Set c = .Cells.Find(What:="#REF!", After:=.Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not c Is Nothing Then

If bCompare = False Then
Set rNextErr = c
Exit For
ElseIf c.Address = sLast Then
bGetNext = True
End If
firstAddress = c.Address

Do
Set c = .Cells.FindNext(c)
If bGetNext Then
If c.Address <> firstAddress Then
Set rNextErr = c
End If
Exit Do
ElseIf bCompare Then
If c.Address = sLast Then
bGetNext = True
End If
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

If Not rNextErr Is Nothing Then Exit For
bCompare = False
bGetNext = False
Next

If Not rNextErr Is Nothing Then
Set mRngLast = rNextErr
mRngLast.Parent.Activate
mRngLast.Select
' mRngLast.Interior.ColorIndex = 3

Exit For
ElseIf bGotOld = False Then
MsgBox "#REF! not found"
Exit For
Else
If MsgBox("Reset and search from beginning?", vbYesNo) _
<> vbYes Then
Exit For
Else
idx = 0
Set mRngLast = Nothing
End If
End If
Next

End Sub

Regards,
Peter T
 
Hi
Please forgive me for intruding but I find the question interesting and I was
wondering if it could be put in a Loop with a Msgbox appearing at each find.
I'm here to learn, I wish I could help.
Regards
John
 
Hi Peter
I tried it and it works great. Now I will keep this one and study it for a
while, it's way pass my knowledge.
Great shot!!
Regards
John
 
Thanks. That works great.

Jan

Peter said:
Have a go with something like the following

' code in a normal module
Private mRngLast As Range

Sub NextRefError()
Dim bGetNext As Boolean, bCompare As Boolean, bGotOld As Boolean
Dim i As Long, j As Long
Dim idx As Long
Dim firstAddress As String, sLast As String
Dim c As Range, rNextErr As Range
Dim ws As Worksheet

For i = 1 To 2
On Error Resume Next
idx = 0
idx = mRngLast.Parent.Index
On Error GoTo 0
If idx > 0 Then
If Not mRngLast.Parent.Parent Is ActiveWorkbook Then
If MsgBox("Do you want to switch to " & _
mRngLast.Parent.Parent.Name & _
vbCr & "Or press No to reset, then try again",
_ vbYesNo) = vbYes Then
mRngLast.Parent.Activate
Else
Set mRngLast = Nothing
Exit Sub
End If
End If

bGotOld = True
bCompare = True

sLast = mRngLast.Address
Else
bCompare = False
idx = 1
End If

For j = idx To ActiveWorkbook.Worksheets.Count
With ActiveWorkbook.Worksheets(j)

Set c = .Cells.Find(What:="#REF!",
After:=.Range("A1"), _ LookIn:=xlValues,
LookAt:=xlPart, _ SearchOrder:=xlByRows,
SearchDirection:=xlNext) If Not c Is Nothing Then

If bCompare = False Then
Set rNextErr = c
Exit For
ElseIf c.Address = sLast Then
bGetNext = True
End If
firstAddress = c.Address

Do
Set c = .Cells.FindNext(c)
If bGetNext Then
If c.Address <> firstAddress Then
Set rNextErr = c
End If
Exit Do
ElseIf bCompare Then
If c.Address = sLast Then
bGetNext = True
End If
End If
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

If Not rNextErr Is Nothing Then Exit For
bCompare = False
bGetNext = False
Next

If Not rNextErr Is Nothing Then
Set mRngLast = rNextErr
mRngLast.Parent.Activate
mRngLast.Select
' mRngLast.Interior.ColorIndex = 3

Exit For
ElseIf bGotOld = False Then
MsgBox "#REF! not found"
Exit For
Else
If MsgBox("Reset and search from beginning?", vbYesNo) _
<> vbYes Then
Exit For
Else
idx = 0
Set mRngLast = Nothing
End If
End If
Next

End Sub

Regards,
Peter T
Click to expand...
 
I have this code:

Sub t()
For Each s In ActiveWorkbook.Sheets
For Each c In s.UsedRange.Cells
If IsError(c) Then
errval = c.Value
If errval = CVErr(xlErrRef) Then
c.Interior.ColorIndex = 3
End If
End If
Next c
Next s
End Sub

It colors all cells red, if the contains the #REF! error. The problem is,
that my client would like it to select the first instans of the error and
stop. When he runs it a second time, it should stop with the second instans
and so on.

I don't see how this can be done?

Jan
Click to expand...

When you write "stop with the second instans" do you still want the first
instance colored?

If so:

======================================
ption Explicit
Sub t()
Dim s As Worksheet
Dim c As Range
For Each s In ActiveWorkbook.Sheets
For Each c In s.UsedRange.Cells
If IsError(c) Then
With c
If .Value = CVErr(xlErrRef) And _
.Interior.ColorIndex <> 3 Then
.Interior.ColorIndex = 3
Exit Sub
End If
End With
End If
Next c
Next s
End Sub
====================================
--ron
 
Back
Top