Converting comments attached to cells into data

[glarosa]

Hi There,

I thought I'd try again to get an answer to this problem. I have a
spreadsheet set up and in one particular column, each cell has a comment
attached. Is there some way that I create a new column and copy each of those
comments into the cells so that they become part of the data in the
spreadsheet? I don't want to cut and paste each individually as there are
hundreds of cells but do them on one go. Is this possible?

Although I received suggestions from Tim and Mike, I could not get either
suggestion to work. Sorry guys. Are there any other suggestions I could try?

 

[Mike H]

Hi,

I missed your response to my post yesterday

Right click your sheet tab, view code and paste this in. Currently it uses
Column A so change to suit. It will copy comments into the cell to the right
of the column you use and then delete the original comment.

You can run it from VB editor by taspping F5 or close VB editor
File|Close and return to..

Then
Tools|Macro|Macros
Select 'This WorkBook'
Highlight the macro name and click run

Sub marine()
Application.ScreenUpdating = False
Set myrange = Range("A1:a100")
For Each c In myrange
On Error Resume Next
c.Offset(, 1).Value = c.Comment.Text
c.ClearComments
Next
Application.ScreenUpdating = True
End Sub

Mike

 

[Tim]

Hi,

First change your security level-Tools>Options>Security>Macro
Security>Choose the “Low†option.

Then Copy this code:

Sub showcomments()
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False

Dim commrange As Range
Dim cmt As Comment
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

Set newwks = Worksheets.Add

newwks.Range("A11").Value = _
Array("Number", "Name", "Value", "Comment")

i = 1
For Each cmt In curwks.Comments
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = i - 1
.Cells(i, 2).Value = cmt.Parent.Name.Name
.Cells(i, 3).Value = cmt.Parent.Value
.Cells(i, 4).Value = cmt.Parent.Address
.Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ")
End With
Next cmt

newwks.Cells.WrapText = False
newwks.Columns.AutoFit

Application.ScreenUpdating = True

End Sub

Go to your book>Alt+F11>Insert>Module and Paste the copied Code.
Close the VBA Editor>Select your worksheet with the comments>Alt+F8> and
Run the macro “showcommentsâ€.
After running the macro it will list all comments from your worksheet
regardless in which column are they on a new Worksheet. If you have comments
in more the 1 column and you insist to list only the comments from that
particular column then use the Mike’s suggestion.
Hope this helps.

Tim