ON Error GoTo Label 'In a loop'

[CodyMonster]

Maybe someone can help me with this?
I have a loop going through a column to see if anything is not zero.
If there is an error in one of the cells I want is to go to the error handler and perform a set of statements.
However, I'm getting issues with the loop not ending as the variable will not end.

For example, if Lastrow = 10, when the next x runs it will continue to go to 11 or forever.
I don't know if I can use the GoTo label as I did. Using a If Than Else statement.

This isn't probably the prettiest script.. but it works (well, used too before I added the error handling).

Dim Lastrow As Integer

Lastrow = Sheets("Prior Data").Cells(Rows.Count, "B").End(xlUp).Row
    Debug.Print Lastrow
    
    For x = 1 To Lastrow
    Debug.Print x
            On Error GoTo ErrorLoop
            Creditlimitamount = Sheets("Prior Data").Cells(x, 5).Value
            If Creditlimitamount <> 0 Then
            Sheets("Prior Data").Cells(x, 2).Resize(1, 8).Copy
            Lastrowdiff = Sheets("differences").Cells(Rows.Count, 2).End(xlUp).Row + 1
            Sheets("differences").Range("B" & Lastrowdiff).PasteSpecial xlValues
            Sheets("differences").Range("B" & Lastrowdiff).PasteSpecial xlFormats
            Sheets("differences").Range("A" & Lastrowdiff).Value = Date
           End If

ErrorLoop:
If Application.WorksheetFunction.IsError(Sheets("Prior Data").Cells(x, 4)) Then
Sheets("Prior Data").Cells(x, 1).Resize(1, 3).Copy
Newcp = Sheets("Historical_Data").Cells(Rows.Count, 30).End(xlUp).Row + 1
Sheets("Historical_Data").Range("AD" & Newcp).PasteSpecial xlValues
Sheets("Historical_Data").Range("AD" & Newcp).PasteSpecial xlFormats

Else
Sheets("Prior Data").Cells(x, 1).Resize(1, 2).Copy
RemoveCp = Sheets("Historical_Data").Cells(Rows.Count, 34).End(xlUp).Row + 1
Sheets("Historical_Data").Range("AH" & RemoveCp).PasteSpecial xlValues
Sheets("Historical_Data").Range("AH" & RemoveCp).PasteSpecial xlFormats
Sheets("Prior Data").Cells(x, 4).Resize(1, 1).Copy
RemoveCp2 = Sheets("Historical_Data").Cells(Rows.Count, 36).End(xlUp).Row + 1
Sheets("Historical_Data").Range("AJ" & RemoveCp2).PasteSpecial xlValues
Sheets("Historical_Data").Range("AJ" & RemoveCp2).PasteSpecial xlFormats


End If

Skip:
Next x

Resume Skip

Thanks for all the help if you can!!!

 

[svendiamond]

What is the purpose of the "Skip" part? Also, what exactly do you want to happen when there is an error? Do you want it to run the "ErrorLoop" code and then stop everything?

 

[shg]

The Resume statement has to be inside the error handler, and code should never 'fall into' an error handler via straight-line execution (which is what your code does), except in the trivial case where it's the last line of a procedure.

 

[svendiamond]

The Resume statement has to be inside the error handler, and code should never 'fall into' an error handler via straight-line execution (which is what your code does), except in the trivial case where it's the last line of a procedure.

Right. Basically you should set it up like this:

On Error GoTo yourErrorHandler

    'your code here

    Exit Sub

yourErrorHandler:
'code

End Sub

 

[CodyMonster]

What is the purpose of the "Skip" part? Also, what exactly do you want to happen when there is an error? Do you want it to run the "ErrorLoop" code and then stop everything?

I took the code from another board which suggested:

Sub Err_Test()

On Error GoTo Err_Handler

Dim X As Long

    For X = 1 To 10

        ' Insert your loop code here

Skip:
    Next X

    Exit Sub

Err_Handler:
    ' Insert your Error Handling Code here

    Resume Skip

End Sub

Not stop everything..
Let's say x = 1 to lastrow - and Lastrow is at row 20 out of 50 rows.
There is an error at row 20. I want it to go through the error handling (errorloop) if than else loop then continue on with original loop with lastrow at 21. There is more code after the error handling so I don't want it to quit.
Make sense?

 

[svendiamond]

On which line could an error occur within your code? I'm wondering because I don't see any lines that look like they would generate an error. Mostly you're just copying and pasting, right? Can you give an example of what type of error you may encounter?

 

[mikerickson]

You are already testing for an error condition inside the loop. The On Error Goto ErrorLoop is not needed.

For x = 1 To Last row
    Debug.Print x
    
    Creditlimitamount = Sheets("Prior Data").Cells(x, 5).Value
    If Creditlimitamount <> 0 Then
        Sheets("Prior Data").Cells(x, 2).Resize(1, 8).Copy
        Lastrowdiff = Sheets("differences").Cells(Rows.Count, 2).End(xlUp).Row + 1
        Sheets("differences").Range("B" & Lastrowdiff).PasteSpecial xlValues
        Sheets("differences").Range("B" & Lastrowdiff).PasteSpecial xlFormats
        Sheets("differences").Range("A" & Lastrowdiff).Value = Date
    End If
    
    If IsError(Sheets("Prior Data").Cells(x, 4)) Then
        Sheets("Prior Data").Cells(x, 1).Resize(1, 3).Copy
        Newcp = Sheets("Historical_Data").Cells(Rows.Count, 30).End(xlUp).Row + 1
        Sheets("Historical_Data").Range("AD" & Newcp).PasteSpecial xlValues
        Sheets("Historical_Data").Range("AD" & Newcp).PasteSpecial xlFormats
    Else
        Sheets("Prior Data").Cells(x, 1).Resize(1, 2).Copy
        RemoveCp = Sheets("Historical_Data").Cells(Rows.Count, 34).End(xlUp).Row + 1
        Sheets("Historical_Data").Range("AH" & RemoveCp).PasteSpecial xlValues
        Sheets("Historical_Data").Range("AH" & RemoveCp).PasteSpecial xlFormats
        Sheets("Prior Data").Cells(x, 4).Resize(1, 1).Copy
        RemoveCp2 = Sheets("Historical_Data").Cells(Rows.Count, 36).End(xlUp).Row + 1
        Sheets("Historical_Data").Range("AJ" & RemoveCp2).PasteSpecial xlValues
        Sheets("Historical_Data").Range("AJ" & RemoveCp2).PasteSpecial xlFormats
    End If

Next x

Also, when one writes error handling, one needs to clear the error with Err.Clear before returning execution to the main routine.

 

[CodyMonster]

I need on error goto because the "if creditlimotamount <> 0 then" runs into an #n/a the code errors out and crashes. The #n/a is a result of a new line of data (which rarely happens) which gets taken care of. Maybe not the best way but kinda what im working with.

 

[mikerickson]

Creditlimitamount = Val(CStr(Sheets("Prior Data").Cells(x, 5).Value))

Will protect against an error value in Cells(x,5) and insure that Credtitlimitamount is numeric.
The CSTR will change an error value (like #DIV/0) into a string (e.g. "Error 2007") and the Val will turn that into 0.

 

[CodyMonster]

To further clarify...the new line of data needs to get taken care of. That is what i was trying to do with the "if than else" in the errorloop:

The other issue is the "next x" doesn't stop at the "x = 1 to last row" if the last row is 50 the "next x" has it loop forever 51...52...53...etc.