Test for comments

Danny Ray

Board Regular
Joined
May 28, 2005
Messages
79
Hello,
I am using the following code to transfer comments to a separate worksheet.
However, I get an error when no comments exist.
Can someone tell me the proper syntax to test for comments?
Thanks.


Code:
Sub Save_Comments()
For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
    Select Case oCell.Comment.Text
    Case Is <> ""
    Sheets("sheet2").Range("a65536").End(xlUp).Offset(1, 0) = oCell.Offset(0, -34)
    Sheets("Sheet2").Range("a65536").End(xlUp).Offset(0, 1) = oCell.Comment.Text
    End Select
Next
For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
oCell.ClearComments
Next
End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
How about an error handler ..

Code:
Sub Save_Comments()
    On Error Goto ErrHandler
    '..
    '.. your code
    '..
    Exit Sub
ErrHandler:
    MsgBox "There are no comments!"
End Sub
 
Upvote 0
Hello Firefytr,
Thank you for your help.
I adapted your suggestion as follows.
Do you foresee any potential problems?


Sub Save_Comments()
On Error GoTo errhandler
Code:
For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
    Select Case oCell.Comment.Text
    Case Is <> ""
    Sheets("sheet2").Range("a65536").End(xlUp).Offset(1, 0) = oCell.Offset(0, -34)
    Sheets("Sheet2").Range("a65536").End(xlUp).Offset(0, 1) = oCell.Comment.Text
    End Select
Next
For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
oCell.ClearComments
Next
errhandler:
Exit Sub
End Sub
 
Upvote 0
I would use it like so ..

Code:
Sub Save_Comments()
    On Error GoTo errhandler
    For Each oCell In Range("al1:al1000").SpecialCells(xlCellTypeComments)
        Sheets("sheet2").cells(rows.count, "A").End(xlUp).Offset(1, 0) = oCell.Offset(0, -34)
        Sheets("Sheet2").cells(rows.count, "A").End(xlUp).Offset(0, 1) = oCell.Comment.Text
            oCell.Clearcomments
        End Select
    Next
    Exit Sub
    errhandler:
End Sub
If you wanted to know more about the error you could add something like this ...
Code:
    '...
    errhandler:
    MsgBox "Number: " & Err.Number & vbNewLine & "Description: " & Err.Description
 
Upvote 0
Hello, guys,
to get a more descriptive error-handling-message you can use something like this
Code:
Sub Save_Comments()
Dim oCell As Range
Dim rng As Range

On Error Resume Next
Set rng = Range("al1:al1000").SpecialCells(xlCellTypeComments)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "no cells with comments found," & vbLf & "nevermind, it's good for your machine not to work to hard :-) ", 48, "title"
Exit Sub
End If

For Each oCell In rng
'etcetera
kind regards,
Erik
 
Upvote 0
Hello again,
I have run into another problem.
How could I edit the following code to allow for no matches in the search range?

Code:
Sub View_Comments()

 For Each cell In Sheets("sheet2").Range("a2:a1000")
      If cell = ActiveCell.Offset(0, -35) Then
        Sheets("Comments Archive").Range("a65536").End(xlUp).Offset(1, 0) = cell
        Sheets("Comments Archive").Range("a65536").End(xlUp).Offset(0, 1) = cell.Offset(0, 1)
      End If
   Next
     Sheets("Comments Archive").Visible = True
     Sheets("Comments Archive").Select
End Sub
 
Upvote 0
Danny Ray said:
Hello again,
I have run into another problem.
How could I edit the following code to allow for no matches in the search range?

Code:
Sub View_Comments()

 For Each cell In Sheets("sheet2").Range("a2:a1000")
      If cell = ActiveCell.Offset(0, -35) Then
        Sheets("Comments Archive").Range("a65536").End(xlUp).Offset(1, 0) = cell
        Sheets("Comments Archive").Range("a65536").End(xlUp).Offset(0, 1) = cell.Offset(0, 1)
      End If
   Next
     Sheets("Comments Archive").Visible = True
     Sheets("Comments Archive").Select
End Sub
this is not clear to me
what do you mean by "allow for no matches" ?
 
Upvote 0
Hello Eric,
Sorry, I should have been more clear.

What I mean is if there are no cells in range a2:a1000 that are equal to
Code:
activecell.offset(0,-35).
As of now, when I run this code, the sheet "Comments Archive" appears with blank cells.
How could I edit the code to exit if no cells are equal to
Code:
activecell.offset(0,-35)
?
 
Upvote 0

Forum statistics

Threads
1,226,504
Messages
6,191,430
Members
453,657
Latest member
DukeJester

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