Cell comments

[jeancake]

I get a file that have numerous cells with comments in column E. I would like to move the comments from their original cells and place them in Column F under a new field called comments. Is there a procedure that can do that?

 

[shg]

How about manually?

Copy col E, select col F, paste special, comments

Select col E again, Clear > Comments

 

[shg]

If you want a macro, ...

Sub x()
    With Range("E:E")
        .Copy
        .Offset(, 1).PasteSpecial Paste:=xlPasteComments
        .ClearComments
    End With
    Application.CutCopyMode = False
End Sub

 

[jeancake]

Not quite what I'm looking for. When I try it your way it gets copied as a comment in Column F, but I would like it to paste it as an actual value in the column as if I had typed it in column F myself.

 

[mcorriga]

I think this will work for you:

Sub MoveComments()
On Error Resume Next
Dim rowCounter, place1 As Long
Dim rawComment, modComment As String
Application.ScreenUpdating = False
'Find the last row to use as a counter
rowCounter = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

Do Until rowCounter = 1
curComment = ""
ActiveSheet.Range("E" & rowCounter).Select
If IsEmpty(ActiveCell.Comment.Text) = True Then
curComment = ""
modComment = ""
Else
curComment = ActiveCell.Comment.Text
'OPTIONAL - remove the name of the commenter
place1 = InStr(1, curComment, ":")
modComment = Mid(curComment, place1 + 1, Len(curComment) - place1)
''''''''''''''''''''''''''''''''''''''''''''
End If
'Move to the right column and place the comment in the field
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = modComment 'Change modComment to rawComment to include name of commenter
'decrease counter
rowCounter = rowCounter - 1
Loop
Application.ScreenUpdating = True
End Sub