Move comment into cell as text

[PhooPhan]

I have a spreadsheet that someone else created and they added comments to a
bunch of cells in one column. I want to convert the comments to text in
either the same cell or one to the right. (the comments are in blank cells)
It's driving me crazy to have to either hover over or click on the comment
mark every time I want to see the notes. I also want to import the
spreadsheet into a Business Manager Database. When I tried to import it the
way it is now the comments did not import.

 

[Mike H]

Hi,
Right click your sheet tab, view code and paste this in and run it.
It takes Column A (Change to suit) and puts the comment as text in the same
cell. Not the 2 commented out lines. The first is the syntax to copy to an
adjacent cell and the second deltes the comment if you want to do that.

Sub sonic()
Set myrange = ActiveSheet.Range("A:A").SpecialCells(xlCellTypeComments)
For Each c In myrange
c.Value = c.Comment.Text
'c.Offset(0, 1).Value = c.Comment.Text
'c.ClearComments
Next
End Sub

Mike

 

[Rick Rothstein]

This macro will put the comments in the same cell it is attached to and then
delete the comments...

Sub MoveCommentsToCells()
Const WorksheetName As String = "Sheet2"
Const CommentColumn As Long = 4
Dim C As Comment
For Each C In Worksheets(WorksheetName).Comments
With C.Parent
If .Column = CommentColumn Then
If UBound(Split(.Comment.Text, vbLf)) > 0 Then .WrapText = True
.Value = C.Text
C.Delete
End If
End With
Next
End Sub

There are two things you have to change in the above so that they apply to
your worksheet (and not my test worksheet). Change the Const assignment for
the WorksheetName to the name you have on the tab for the worksheet with
your comments on them. Also change the Const assignment for the
CommentColumn from the test column number of 4 that I used in my test to
whatever the column number is with your comment in it. If you are unfamiliar
with macros, simply right click the worksheet tab your comments are on,
select View Code from the popup menu that appears and copy/paste the above
macro into the code window that appeared. You can then make the two changes
I just told you about. Finally, place the cursor anywhere inside the code
you just pasted and press F5. Go back to your worksheet... the comments
should now be in the cells (by the way, if your comments occupied more than
one line, then text wrapping for the cell was turned on).

 

[PhooPhan]

Hi Rick,

Unfortunately it didn't work. I double and triple checked your instructions
and no go. Not sure if this matters but I'm running Vista Home Premium and
MS Excel 2007.

Thanks.

 

[PhooPhan]

Rick,

Never mind. I got it to work. There was a hidden column so I entered the
wrong value.

Thank you for this!