Error Trapping Issues

[Alan]

I have what I thought would be a very simple loop: I am trying to copy
the comments from the cells in row (rRow-1) to row (rRow) for columns
3 to 26. Some of the cells in rRow-1 have comments others do not so I
thought that I would use simple error trapping to help me to navigate.

Columns 3 to 13 contain comments and all is fine, column 14 has no
comment and again the code works, column 15 has no comment and the
script blows freezes on the line

Txt = .Cells(rRow - 1, i).Comment.Text

To make matters worse, subsequent cells that contain comments then
also blow up on

.Cells(rRow, i).AddComment

Could you offer any pointers about where I am going wrong?


For i = 3 To 26
On Error GoTo 1
' Does a comment exist to copy? (no comment, skip to Next)
Txt = .Cells(rRow - 1, i).Comment.Text
' Does a comment exit to paste to?
On Error Resume Next
.Cells(rRow, i).AddComment
On Error GoTo 0
' Insert comment text
.Cells(rRow, i).Comment.Text Text:=Txt
1: Next i

 

[Chip Pearson]

In the context of error handling, VBA code runs in one of two "modes":
normal mode and error mode. When an error is raised, VBA operates in
error mode, and no additional handling can take place. You can get out
of error by using the Resume statement. Unless you Resume, no
additional error handling can be undertaken since your code is still
running in error mode. A simple example will illustrate this:

Sub AAA()
Dim N As Long
Dim D As Double
On Error GoTo ErrH:
For N = 0 To 3
D = Cells(1, 1) / N
1: Next N
Exit Sub
ErrH:
Resume 1
End Sub

Here, the first iteration of the loop (N =0) will cause a Division By
Zero error, so the code jumps to the ErrH: label. There, it encounters
the Resume 1 statement that clears error mode and resumes normal mode,
and then executes the Next N statement.

You can adapt this general structure to your code. See
http://www.cpearson.com/Excel/ErrorHandling.htm for information about
error handling.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[got.sp4m]

So what he probably should do is something in this direction:

For i = 3 To 26
Txt = ""
On Error Resume Next
Txt = .Cells(rRow - 1, i).Comment.Text
On Error Goto 0 'Alternatively any "On error Goto <label>"
'statement you used prior to the
'"On Error Resume Next" statement
if Txt <> "" then
.Cells(rRow, i).AddComment
.Cells(rRow, i).Comment.Text Text:=Txt
End If
Next i

Right?

best regard
Peder Schmedling

 

[Alan]

Thanks for your example and clear description Chip. My routine is now
all fixed and working.

 

[Alan]

So what he probably should do is something in this direction:

   For i = 3 To 26
      Txt = ""
      On Error Resume Next
      Txt = .Cells(rRow - 1, i).Comment.Text
      On Error Goto 0 'Alternatively any "On error Goto <label>"
                      'statement you used prior to the
                      '"On Error Resume Next" statement
      if Txt <> "" then
          .Cells(rRow, i).AddComment
          .Cells(rRow, i).Comment.Text Text:=Txt
      End If
  Next i

Right?

best regard
Peder Schmedling

What I actually did was :

For i = 3 To 26
On Error GoTo NoComment
' Does a comment exist to copy?
Txt = .Cells(rRow - 1, i).Comment.Text
' Does a comment exit to paste to?
On Error Resume Next ' to allow for the fact that a comment
may already exist
.Cells(rRow, i).AddComment
On Error GoTo 0
' Insert comment text
.Cells(rRow, i).Comment.Text Text:=Txt
Txt = ""
1: Next i


NoComment:
Resume 1

 

[Dave Peterson]

Maybe you could just check for the comment and drop the error checking
completely:

I'm not sure I got the logic of what you want, but this may get you started:

Option Explicit
Sub testme()
Dim rRow As Long
Dim i As Long

rRow = 7
With ActiveSheet
For i = 3 To 26
'Check to see if sending cell has a comment
If .Cells(rRow - 1, i).Comment Is Nothing Then
'no comment in receiving cell, skip it
Else
'check for receiving cell
'don't overwrite any existing comment
If .Cells(rRow, i).Comment Is Nothing Then
'no comment, so add it to next row
.Cells(rRow, i).AddComment _
Text:=.Cells(rRow - 1, i).Comment.Text
End If
End If
Next i
End With
End Sub

Or to just overwrite any existing comment:

Option Explicit
Sub testme2()
Dim rRow As Long
Dim i As Long

rRow = 7
With ActiveSheet
For i = 3 To 26
'Check to see if sending cell has a comment
If .Cells(rRow - 1, i).Comment Is Nothing Then
'no comment in receiving cell, skip it
Else
'just delete any existing comment
'and plop new comment in
.Cells(rRow, i).Comment.Delete
.Cells(rRow, i).AddComment _
Text:=.Cells(rRow - 1, i).Comment.Text
End If
Next i
End With
End Sub

 

[Dave Peterson]

This note in the code:
'no comment in receiving cell, skip it
is wrong.

It should have been:
'no comment in Sending cell, skip it

But the code did what I intended.

 

[Alan]

This note in the code:
'no comment in receiving cell, skip it
is wrong.

It should have been:
'no comment in Sending cell, skip it

But the code did what I intended.














--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave, I understood what you were intending and the code is a
nice alternative to what I have already got.

Cheers ...

 

[Dave Peterson]

I think it's a good idea to understand how error handling works, but if you can
accomplish what you need without the error checking, I think it's easier to
understand.