Error Handling in a Loop

Engineer Joe

Engineer Joe

Well-known Member
Joined
Jun 10, 2005
Messages
549
I'm running a loop, and in the loop i refer to a range (via offset) that may not exist...for example if i use RNG as a range variable and then refer to RNG.offset(0,-1), this gives me an error if the range is in column A. I use an error statement to bypass the whole process

Code: 
On Error Goto NoRef:

It works the first time that i execute the loop, but never the second time. It always brings up the error statement 1004 (presumably indicating that i'm trying to reference an object that doesn't exist.)It's like the On Error statement is only good for one time. Is there a line of code i have to use to reinitiate the On error statement. Is it because I've already thrown an error once? This has never happened me before with an error, but it could be because i've never had more than one error in a procedure... Is there something i must do to set it up again. Remember, it always works the first time i throw an error, but not the second. :oops:
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
You need something like
Code: 
Exit Sub  'need this above the error handler so it does not fire when sub is successful
NoRef:
'error handling code

Err.Clear
Resume  Next 'these clear the error and send it back


That works for me in a general sense...depends on what your error handler needs to do.....
 
Upvote 0
I've been using Err.clear, and i added a watch and saw that it reset error to 0. But it is still firing the error window when i loop through the second time....it doesn't make sense...
 
Upvote 0
Why not try and eliminate the error?
 
Upvote 0
Yes, that's a logical solution...

What, praytell, is the best way to elimate the error? My target range may not exist since it's an offset(-1,0)...maybe an if/then statement that only operates in the event that my main targe range is in Column B or greater....
 
Upvote 0
Joe

Can't really give any specific advice without seeing the code and what it's
intended to do?:)
 
Upvote 0
Just put in an if/then:

Code: 
If CellIQ.column >=3 then

I was using the offset of CellIQ....CellIQ.offset(0,-2) in some instances...

And it worked. So, yes, it solved the problem, but no, it doesn't comfort me that my errorhandling line didn't work... It doesn't make sense that it should work the first time through a loop but not the second time.... The error is 1004. That's the error for "You've tried to use an Object that doesn't exist, YOU IDIOT!", among others. Basically, it was pointing out that I was trying to assign a value to a range that doesn't exist. I guess it's just the point of the matter. So, I put in the If/then statement that no longer allows the error, but I don't feel like i learned anything. And that's a sad thing when it's such an interesting error...wouldn't you agree? I guess i'm just hungry for knowledge... :p

sorry...just had to use an icon, so i used a stupid cliche...i promise it won't happen again.
 
Upvote 0
Hello, Joe,
long ago we met :)

here some stuff I found in my libraries on OnError

Code: 
Public Sub GotoSheet()
On Error GoTo NoSheet
TryAgain:

ShName = Application.InputBox("Please Type in Sheet Name", "Goto Sheet")
If ShName = False Then Exit Sub
Sheets(ShName).Select
Exit Sub

NoSheet:
Resume TryAgain
End Sub

Code: 
Sub test()
shn = "kilimanjaro"
On Error Resume Next
Sheets(shn).Select
MsgBox "did you see the sheet " & shn & "?", 32, UCase(shn)
On Error GoTo skip
Sheets(shn).Select
MsgBox "not displayed"
skip:
MsgBox "did you see the sheet " & shn & "?", 32, "SKIP"
Sheets(shn).Select 'error occuring
End Sub

Public Sub ErrorDemo() 
On Error GoTo TheErrHandler 

    ' FIRST ERROR 
8     MakeError = 5 / 0 
    MsgBox "I'm back  1" 
    
    ' SECOND ERROR 
22     MakeError2 = 5 / 0 
    MsgBox "I'm back  2 " 
    
    Exit Sub 

TheErrHandler: 

    ' HOW TO HANDLE FIRST ERROR 
    If Erl = 8 Then Resume Next 
    
    ' HOW TO HANDLE SECOND ERROR 
    If Erl = 22 Then Exit Sub 

End Sub
Code: 
On Error Resume Next 
....
On Error GoTo 0

when you try to divide by zero you get an error
errors need to be "handled" else the code can't go any further

On Error Resume Next tells to jump to the next instruction
FINE CODE :wink: BUT :cry: you can imagine the problems if a lot of instructions are skipped whithout you want this to happen

an other way to handle errors is to send the code to another line
on Error GoTo skip

Code: 
Sub test()
shn = "kilimanjaro"
On Error Resume Next
Sheets(shn).Select
MsgBox "did you see the sheet " & shn & "?", 32, UCase(shn)
On Error GoTo skip
Sheets(shn).Select
MsgBox "not displayed"
skip:
MsgBox "did you see the sheet " & shn & "?", 32, "SKIP"
Sheets(shn).Select 'error occuring
End Sub

On Error GoTo 0 means you stop the error handling from there
If you don't do this and your code contains errors you won't see them :unsure:

I like using "resume next" just before the potential problem and "closing the on error" using "goto 0"
Code: 
Sub test()
'does range exist
Dim i As Integer
Dim c As Range

For i = -1 To 3
Set c = Nothing
    On Error Resume Next
    Set c = Cells(i, 1)
    On Error GoTo 0
If Not c Is Nothing Then Cells(i, 1).Interior.ColorIndex = 3
Next i

End Sub

kind regards,
Erik
 
Upvote 0
Howdy Eric! I'll post the code that was causing problems, so that everyone can see. Also, it was giving me Error 1004 on the first line of code that i used an offset. I understand this, because my offset was refering to an object that did not exist. Here is the code....

Code: 
 CellIQ.Offset(0, -2).Interior.ColorIndex = 4
            
            If Replaceref <> "" Then
                
                Select Case MsgBox("Would you like to also replace reference column values?" _
                                   & vbCrLf & vbCrLf & "Reference to Replace:  " & CellIQ.Offset(0, -2).Value _
                                   & vbCrLf & vbCrLf & "Reference to Use:  " & Replaceref _
                                   , vbYesNo Or vbQuestion Or vbDefaultButton1, "Reference Column Replacement?")
                
                    Case vbYes
                        CellIQ.Offset(0, -2).Value = Replaceref
                                
                    Case vbNo
                End Select
        
            End If
            CellIQ.Offset(0, -2).Interior.ColorIndex = xlNone

There are several fixes to this. The one i'm using now was a suggestion of norie's. I simply didn't let the code run unless CELLIQ was in column C or up, because if CELLIQ is in A or B, I get the error. Another solution that I used was using the On Error Resume Next. I looked at each line of code within the code, and since each code would generate an error, that solution would work alright too. BUT THE SOLUTION THAT WOULD NOT WORK IS THIS.... I'LL post the entire procedure....
Code: 
Sub Finder(Lookfor, PartorWhole, Replacewith, Replaceref)
Dim CellIQ As Range
Dim RefCell As Range
Dim RefSet As Boolean


' INITIATE LOOP __________________________________
RefSet = False
Set RefCell = Range("Z90")

Set CellIQ = Cells.Find(What:=Lookfor, SearchOrder:=xlByRows, SearchFormat:=False, LookAt:=PartorWhole)

'START LOOP
Do Until (CellIQ Is Nothing)
    If CellIQ = RefCell Then GoTo Finished:
    

'______VERIFIES SEARCHED CELL CONTENT IS FOUND AND PROMPTS USER TO VERIFY REPLACEMENT

    If (Replacewith <> "") Then
        CellIQ.Select
        CellIQ.Interior.ColorIndex = 4
        
        Select Case MsgBox("Would you like to replace this cell's content...." _
                           & vbCrLf & vbCrLf & CellIQ.Value _
                           & vbCrLf & vbCrLf & "with the following content...." _
                           & vbCrLf & vbCrLf & Replacewith _
                           , vbYesNo Or vbQuestion Or vbDefaultButton1, "Found Matching Text")
        
            Case vbYes
                CellIQ.Value = Replacewith
                        
            Case vbNo
                If RefCell = Range("Z90") And RefSet = False Then
                    Set RefCell = CellIQ
                    RefSet = True
                End If
                
        End Select
        CellIQ.Interior.ColorIndex = xlNone
        
    
    '______ASKS USER (IF REFERENCE VALUE WAS INPUT IN CELL REPLACER) TO VERIFY REPLACEMENT OF PERTINENT REFERENCE DATA)
        
        On Error Goto NothingFound:
        
            CellIQ.Offset(0, -2).Interior.ColorIndex = 4
            
            If Replaceref <> "" Then
                
                Select Case MsgBox("Would you like to also replace reference column values?" _
                                   & vbCrLf & vbCrLf & "Reference to Replace:  " & CellIQ.Offset(0, -2).Value _
                                   & vbCrLf & vbCrLf & "Reference to Use:  " & Replaceref _
                                   , vbYesNo Or vbQuestion Or vbDefaultButton1, "Reference Column Replacement?")
                
                    Case vbYes
                        CellIQ.Offset(0, -2).Value = Replaceref
                                
                    Case vbNo
                End Select
        
            CellIQ.Offset(0, -2).Interior.ColorIndex = xlNone
                
        End If
        
             
    Else: GoTo NothingFound:
    
    End If

NothingFound:    
    Set CellIQ = Cells.FindNext(CellIQ)

Loop


NothingFound:

Finished:
End Sub

I couldn't copy my code exactly, i threw in the Error statements the way i remember them....but I'm trying to make the point that the first time i run through the loop, the error gets thrown, so it goes to NothingFound: Then the next time it loops through, the VERY SAME ERROR STATEMENT IS THROWN. But instead of GOING TO, it decides to bring up the error screen... I've even tried throwing in the Err.clear, to clear the Err property. It seems very strange to me that while going through the loop, that it should behave the way i want it to on the first pass, but then acts like i didn't put an errorhandler in the second time.
 
Upvote 0
you call it an errorhandler but in fact it doesn't handle the error itself
you're just giving a new instruction

while VBA is screaming for a solution for the first error - because you told it to look for a handling "On Error Goto ..." - you trigger another error

that's the reason On Error Resume Next exists: "don't scream for handling but just continue!"

in the next example the error is handled by clearing it



Code: 
Option Explicit

Sub test()
Dim i As Integer
On Error GoTo msg

    For i = -1 To 2
    Cells(i, 1).Activate
    Application.Wait Now + 1 / 60 / 60 / 24
    Next i
Exit Sub
msg:
MsgBox "row " & i & " does not exist", 48, "error"
Err.Clear
Resume Next
End Sub
another example
Code: 
Sub test()
Dim i As Integer

On Error GoTo skip
For i = 1 To 3
MsgBox i / 0
skip:
    If Err Then
    MsgBox "ERROR " & Err.Number & vbLf & Err.Description, 48, "i = " & i
    Err.Clear
    Resume Next
    End If
Next i
End Sub

kind regards,
Erik
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Method 'SaveAs of Object'_Workbook' failed error handling routine help needed.
Replies
3
Views
577
rlv01
R
S
  • Solved
VBL loop help
Replies
3
Views
150
mse330
M
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
352
mumps
mumps
C
  • Solved
How to use conditional compiling in VBA
Replies
4
Views
135
Jon Peltier
J
M
  • Question
Clipboard error when copying shape
Replies
4
Views
498
marc81
M

Forum statistics

Threads
1,215,681
Messages
6,126,194
Members
449,298
Latest member
Jest

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top