How do I copy a comment to become the information in a cell.

[Patrick from RAS]

I am trying to copy comments in a spreadsheet into cells on the same line so
that they become the cell not a comment attached to a cell.

[Rowan]

The macro below inserts the text from a comment in the activecell into the
first blank cell in the row. The comment is then deleted so save your work
before testing this:

Sub cmt()
Dim eCol As Integer
Dim cmtText As String
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If Not cmt Is Nothing Then
With ActiveCell
eCol = Cells(.Row, Columns.Count).End(xlToLeft).Column + 1
cmtText = Application.WorksheetFunction.Substitute _
(cmt.Text, Chr(10), " ")
Cells(.Row, eCol).Value = cmtText
Cells(.Row, eCol).WrapText = False
cmt.Delete
End With
End If
End Sub

Hope this helps
Rowan

"Patrick from RAS" wrote:

> I am trying to copy comments in a spreadsheet into cells on the same line so
> that they become the cell not a comment attached to a cell.