Why does the error handler work only one time?

[TomCon]

I am trying to move comments 3 columns to the right and then delete the comment from the source cell.

The following code works to do that if the cell that is referenced by range r2 contains a comment.

If the cell does not contain a comment, the very first time through the 'i' loop when the statement

txt = r2.Comment.Text

is hit, the code will jump to the label nocom: (which is the behavior that i desire). However, the next time through the i loop, the code hits that statement and does not jump to the label nocom:. Instead it gives the message Run timeError 91, "Object variable or With block variable not set". Why does it do the jump one and only one time? Suggestions for how can this be accomplished? Thank you.

Sub Move_Comment()
Dim r As Range
Dim r2 As Range
Dim txt As String
comoffset = 3
Set r = Range("BK12")
For i = 0 To -24 Step -1
    Set r2 = Range(r.Offset(0, i).Address)
    On Error GoTo nocom
        txt = r2.Comment.Text
        r2.Offset(0, comoffset).AddComment
        r2.Offset(0, comoffset).Comment.Text Text:=txt
        r2.ClearComments
nocom:
txt = ""
Next i
End Sub

 

[dmt32]

You need to clear the error

Try adding line shown in bold to your code & see if resolves your issue

Sub Move_Comment()
Dim r As Range
Dim r2 As Range
Dim txt As String
comoffset = 3
Set r = Range("BK12")
For i = 0 To -24 Step -1
    Set r2 = Range(r.Offset(0, i).Address)
    On Error GoTo nocom
        txt = r2.Comment.Text
        r2.Offset(0, comoffset).AddComment
        r2.Offset(0, comoffset).Comment.Text Text:=txt
        r2.ClearComments
nocom:
txt = ""
On Error GoTo -1
Next i
End Sub

Dave

 

[Fluff]

Why does it do the jump one and only one time?

Have a look here On Error WTF?

 

[TomCon]

You need to clear the error

Try adding line shown in bold to your code & see if resolves your issue

Sub Move_Comment()
Dim r As Range
Dim r2 As Range
Dim txt As String
comoffset = 3
Set r = Range("BK12")
For i = 0 To -24 Step -1
    Set r2 = Range(r.Offset(0, i).Address)
    On Error GoTo nocom
        txt = r2.Comment.Text
        r2.Offset(0, comoffset).AddComment
        r2.Offset(0, comoffset).Comment.Text Text:=txt
        r2.ClearComments
nocom:
txt = ""
On Error GoTo -1
Next i
End Sub

Dave

Thanks much. That does fix it. Nice to find out this solution! Thanks for a very nice tip for now and future use!

 

[dmt32]

Thanks much. That does fix it. Nice to find out this solution! Thanks for a very nice tip for now and future use!

Rather than On Error GoTo Label, most probably would have used On Error Resume Next as it clears the error but when you do send error handling to a label, Goto -1 is needed to fully clear it.

Plenty of material on web worth reading up on and @Fluff has provided one such link

Glad we were able to help

Dave

 

[mikerickson]

Err.Clear is an alternative to On Error Goto -1

 

[dmt32]

Err.Clear is an alternative to On Error Goto -1

Always been my understanding that Using Clear just clears the text and numbers in the Err object but will NOT reset the error & most likely, would not resolve OPs issue.

Dave

 

[mikerickson]

Hmm... i did some more testing and you are right.
When I use the Err.Clear, the error at i=3 doesn't stop things but when i=6 it pops up.
When I use the On Error Goto -1, that clears the error and the loop goes all the way

Sub test()
    Dim i As Long
    On Error GoTo Handle
    For i = 1 To 10
        MsgBox (1 / (i Mod 3)) & " test"
12:
    Next i
Exit Sub
Handle:
    If MsgBox(i, vbYesNo) = vbNo Then Exit Sub
   ' Err.Clear
   On Error GoTo -1
   GoTo 12
End Sub

But what I discovered is that Resume and Resume Next the also clears the error.

Sub test()
    Dim i As Long
    On Error GoTo Handle
    For i = 1 To 10
        MsgBox (1 / (i Mod 3)) & " test"
    Next i
Exit Sub
Handle:
    If MsgBox(i, vbYesNo) = vbNo Then Exit Sub
   Resume Next
End Sub