Excel comments 1

[simoncpage2]

I have an excel sheet with lots of comments and I'm trying to automate taking out the comments and put them in a cell (automatically) in the cell next.

Any ideas on how I can do this?

Thanks

Simon

 

[VBAjedi]

Here's the idea - note that you get an error if you attempt to refer to the comment object of a cell that has no comment (thus the error-handler override):

Dim C As Range
On Error Resume Next ' Ignores error if no comment present
For Each C In Range("A1:Z100")
   C.Offset(0, 1).Value = C.Comment.Text
Next C
On Error GoTo 0 ' reset error handler

Let me know if that does what you are after!

VBAjedi

 

[VBAjedi]

Here's the general idea - note that you get an error if you attempt to refer to the comment object of a cell that has no comment (thus the error-handler override):

Dim C As Range
On Error Resume Next ' Ignores error if no comment present
For Each C In Range("A1:Z100")
   C.Offset(0, 1).Value = C.Comment.Text
Next C
On Error GoTo 0 ' reset error handler

Let me know if that does what you are after!

VBAjedi

 

[simoncpage2]

I made a function up but thanks for the help!! nice one!


Function GetCommentText(rCommentCell As Range)
Dim strGotIt As String
On Error Resume Next
strGotIt = WorksheetFunction.Clean _
(rCommentCell.Comment.Text)
GetCommentText = strGotIt
On Error GoTo 0
End Function

 

[PHV]

And what about this ?
For Each cmt In ActiveSheet.Comments
cmt.Parent.Offset(0,1).Value = cmt.Text
cmt.Delete
Next cmt
You may have to browse the Comments collection down (last to first).

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBAjedi]

That's nice, PH! Avoids the need for the error handler, and should run a little faster since it doesn't have to loop through all the cells in the range.

VBAjedi