Error 0 because of GoTo

[awsumchillicrab]

Hi,

I have a code that GoTo a place called "Err_TrimSpaces" in event of error during a loop. After the loop is complete, I get the error "0". I found that if I put an "Exit Sub" before the Err_TrimSpaces, then my code will avoid that error. So my question is, how do I get my code to skip Err_TrimSpaces once the loop is complete so I can proceed to process more code - written as ABCDE below?

sub test()

Dim LastRow As Integer
LastRow = Range("a1").CurrentRegion.Rows.Count

Range("e1").Select
On Error GoTo Err_TrimSpaces
Do Until ActiveCell = Range("E" & LastRow)
ActiveCell = Trim(ActiveCell)
ActiveCell = Int(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

Exit Sub

Err_TrimSpaces:
If Err.Number = 13 Then 'data mismatch ie not number
Resume Next
Else
MsgBox Err.Number
End If

ABCDE

end sub

 

[tweedle]

Hop over it

sub test()
Dim LastRow As Integer
LastRow = Range("a1").CurrentRegion.Rows.Count
Range("e1").Select
On Error GoTo Err_TrimSpaces
Do Until ActiveCell = Range("E" & LastRow)
ActiveCell = Trim(ActiveCell)
ActiveCell = Int(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
[COLOR=blue]Goto SkipErrTrap[/COLOR]
Err_TrimSpaces:
If Err.Number = 13 Then 'data mismatch ie not number
Resume Next
Else
MsgBox Err.Number
End If
[COLOR=blue]SkipErrTrap:[/COLOR]
ABCDE
end sub

 

[xenou]

Also, you can just put ABCDE after the loop.

Sub test()
Dim LastRow As Integer

    LastRow = Range("a1").CurrentRegion.Rows.Count
    
    Range("e1").Select
    On Error GoTo Err_TrimSpaces
    
    Do Until ActiveCell = Range("E" & LastRow)
        ActiveCell = Trim(ActiveCell)
        ActiveCell = Int(ActiveCell)
        ActiveCell.Offset(1, 0).Select
    Loop
    
    ABCDE
    
    Exit Sub

Err_TrimSpaces:
If Err.Number = 13 Then 'data mismatch ie not number
    Resume Next
Else
    MsgBox Err.Number
End If

End Sub

However, you don't need the error handling necessarily:

Sub test()
Dim LastRow As Integer

    LastRow = Range("a1").CurrentRegion.Rows.Count
    
    Range("e1").Select
    
    Do Until ActiveCell = Range("E" & LastRow)
        If Not IsNumeric(ActiveCell) Then
            Trim (ActiveCell)
        End If
        If IsNumeric(ActiveCell) Then
            ActiveCell = Int(ActiveCell)
        End If
        ActiveCell.Offset(1, 0).Select
    Loop


End Sub

Or ... you can use "inline" error handling:

Sub test2()
Dim LastRow As Integer

    LastRow = Range("a1").CurrentRegion.Rows.Count
    
    Range("e1").Select
    
    On Error Resume Next
    Do Until ActiveCell = Range("E" & LastRow)
        ActiveCell = Trim(ActiveCell)
        ActiveCell = Int(ActiveCell)
        ActiveCell.Offset(1, 0).Select
    Loop
    On Error GoTo 0

End Sub

ξ

 

[awsumchillicrab]

Thanks guys & girls.

Spaghetti time!