Find errors in Excel.
 

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

Find errors in Excel.
 

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.


"Jan Kronsell" wrote:

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



.

Find errors in Excel.
 

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.


"Mike H" wrote:

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.


"Jan Kronsell" wrote:

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



.

Find errors in Excel.
 

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


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

Find errors in Excel.
 

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

Find errors in Excel.
 

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
"Peter T" <peter_t@discussions wrote in message
...
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


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

Find errors in Excel.
 

Thanks. That works great.

Jan

Peter T wrote:
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


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

Find errors in Excel.
 

On Wed, 17 Feb 2010 11:16:08 +0100, "Jan Kronsell"
wrote:

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



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