Move comments into a cell

[RC]

I have a spreadsheet with data inserted as comments in several cells. Is there a way to move the commnets into a seperate cell or a macro that will move the comments a cell at a time?

Thanks,
Rey Contreras
Phone: 817-763-6356
Email: rey.contreras@LMCO.com

[sen_edp]

Hi there

John Walkenbach gave this tip
---------------------------------------
One way to accomplish this is to use a custom VBA function. Copy the
procedure below to a VBA module in your workbook:

Function CellComment(cell As Range) As String
On Error Resume Next
CellComment = cell.Comment.Text
If Err <> 0 Then CellComment = ""
End Function

Then, you can create a formmula such as:

=CellComment(A1)

This formula displays the comment in cell A1. If it has no comment, it
returns an empty string.
--------------------------------------------

Hope that helps

[Ivan F Moala]

On 2002-05-21 05:36, RC wrote:
I have a spreadsheet with data inserted as comments in several cells. Is there a way to move the commnets into a seperate cell or a macro that will move the comments a cell at a time?

Thanks,
Rey Contreras
Phone: 817-763-6356
Email: rey.contreras@LMCO.com

Hi Rey
Try somthing like this:
Note: What it does is to get ALL commennts.
Copy text AND Place it in the Cell next to it. Please amend to your criteria.

Sub ExtractComments()

Dim rComm As Range

Dim Comm

Dim sTmp As String



Set rComm = Cells.SpecialCells(xlCellTypeComments)



For Each Comm In rComm

    '// get Comment text string

    sTmp = Comm.Comment.Text

    '// Move it to cell next

    Comm.Offset(0, 1) = sTmp

    '// Now delete the comment

    Comm.Comment.Delete

Next

Set rComm = Nothing

End Sub