How do I copy comments into cells

[Guest]

I received a spreadsheet from a customer with a lot of cell comments. It is
difficult to read and see the comments. Is there a way to highlight the
column and copy the comments into cells in the next column?!?!? Not as
comments, but as cell data?!?!?

 

[Dave Peterson]

Saved from a previous post:

You can retrieve the text from a comment with a userdefined function like:

Option Explicit
Function GetComment(FCell As Range) As Variant
Application.Volatile

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetComment = ""
Else
GetComment = FCell.Comment.Text
End If

End Function

Then you can use it like any other function:

=getcomment(a1)

But be aware that the function won't evaluate when you just change the comment.
It'll be correct when excel recalculates. (Hit F9 to force a recalc.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Sean

Macro to do so.

Sub ListComms()
Dim oCell As Range
For Each oCell In Selection
If Not oCell.Comment Is Nothing Then
oCell.Offset(0, 1).Value _
= oCell.Comment.Text
oCell.EntireRow.AutoFit
End If
Next oCell
End Sub

Why are the comments hard to read? Volume of text is too great to see all at
once?

Maybe re-sizing the comments to fit all the text would be easier.

Public Sub Comment_Size()
Dim cmt As Comment
Dim cmts As Comments
Set cmts = ActiveSheet.Comments
For Each cmt In cmts
cmt.Shape.TextFrame.AutoSize = True
Next
End Sub


Gord Dibben Excel MVP