Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
Stopping errors when a find statement doesn't find! | Excel Programming | |||
To find errors | New Users to Excel | |||
VBA- Find Errors | Excel Programming | |||
Find errors | Excel Programming |