Moving a Comment into a Cell - automaticlly

[Guest]

Hello,

I have a spreadsheet with over 7000 rows. one of the rows, Column E,
periodically has comments attached to it. Can I write a VBA or Macro to
take the comments assigned to the cells in Column E and move them into a new
column as text. I wish to do this since I am exporting the file to text so I
may import it into Access, and I do not want to lose the info in the comment
field.

How to I copy the comment from E1 and place it in text in column F1. Then
automate this process to repeat for all 7000 rows.

Thank you in advance for your help.
Mike

 

[Guest]

Sub comment_setter()
Dim r As Range, rr As Range
On Error Resume Next
Set r = Range("E:E").SpecialCells(xlCellTypeComments)
If r Is Nothing Then
MsgBox ("no comments found")
Exit Sub
End If

For Each rr In r
rr.Offset(0, 1).Value = rr.comment.Text
Next
End Sub

 

[Guest]

This worked brilliantly! Thank You.

Sub comment_setter()
Dim r As Range, rr As Range
On Error Resume Next
Set r = Range("E:E").SpecialCells(xlCellTypeComments)
If r Is Nothing Then
MsgBox ("no comments found")
Exit Sub
End If

For Each rr In r
rr.Offset(0, 1).Value = rr.comment.Text
Next
End Sub