Print Cell comments in next column as text entry

[Reimer]

Hi,
I did not have any luck searching so figured I would ask. A co-worker has a workbook with many sheets. Many of the cells have comments. It has been decided it would be better if the information from the cell comment were entered as text in the cell to the right (omitting the name of the originator of the comment). I thought I had seen code for this type of thing but cannot find it now. It would be safer to have the code process one column at a time (as selected by the user). I tried recording a macro but that did not work well ;-). Any chance I could get some help on this one?

[HansV]

Here is a macro you can use:

Code: Select all

Sub ListComments()
  Dim oCell As Range
  Dim rng As Range
  Dim strComment As String
  Dim intPos As Integer
  ' Avoid error if there are no comments
  On Error GoTo ExitHere
  ' Limit to one column
  Set rng = Selection.Columns(1)
  ' Insert cells to the right
  rng.Offset(0, 1).Insert Shift:=xlShiftToRight
  ' Loop through cells with comments
  For Each oCell In rng.SpecialCells(xlCellTypeComments)
    ' Get comment
    strComment = oCell.Comment.Text
    ' Strip away first line
    intPos = InStr(strComment, vbLf)
    If intPos > 0 Then
      strComment = Mid(strComment, intPos + 1)
    End If
    ' Enter in cell to the right
    oCell.Offset(0, 1) = strComment
  Next oCell
ExitHere:
End Sub

The code strips away the first line of each comment on the assumption that it is the author name. If the author name (or the line feed after it) has been removed, the result will not be correct.

[Reimer]

Thanks Hans, I will test it out and run it by the Engineer that requested it.

[Reimer]

Hans,

I tested it and was surprised. The part of the code that says Insert cells to the right, did not seem to work, hence the comments write over the data in the cell to the right. This is not a major problem as I will tell them to make sure they insert a column to the right of the cells with comments.
Thanks so much.

[HansV]

You are supposed to select the cells with the comments. The code will insert blank cells to the right of (the first column of) the selection.

[Reimer]

Thanks Hans
You made one Engineer very HAPPY.