Extract Comments and paste as values

[Btibert]

Hello all,

Hopefully there is a quick and easy answer to this question, but here it goes. Some cells in our Excel worksheet have comments. What we would like to do is take the "text" that is in the comment and extract it to its own cell.

For example, if Cell B1 has "verify address" as its comment, we would like the value of cell C1 to be verify address. Simply put, we want to take the information that is in the comment and place that in its own cell.

Is there an easy way to do this?

Thank you in advance!!

~Brock

[Gord Dibben]

Brock

Macro to do this.

Sub Show_Comments()
Dim Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Not Cell.Comment Is Nothing Then
Cell.Offset(0, 1).Value = Cell.Comment.Text
End If
Next Cell
End Sub

You must be sure that the cell to the right of any comment is empty or it will
be overwritten.

May be better to list all the comments and cell addresses on another sheet.

From Debra Dalgleish............

Sub ListComms()
Dim Cell As Range
Dim sh As Worksheet
Dim csh As Worksheet
Set csh = ActiveWorkbook.Worksheets.Add
csh.Name = "Comments"
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> csh.Name Then
For Each Cell In sh.UsedRange
If Not Cell.Comment Is Nothing Then
With csh.Range("a65536").End(xlUp).Offset(1, 0)
.Value = sh.Name & " " & Cell.Address
.Offset(0, 1).Value = Cell.Comment.text
End With
End If
Next Cell
End If
Next sh
End Sub


Gord Dibben Excel MVP

On Fri, 30 Sep 2005 12:53:43 -0500, Btibert
<[email protected]> wrote:

>
>Hello all,
>
>Hopefully there is a quick and easy answer to this question, but here
>it goes. Some cells in our Excel worksheet have comments. What we
>would like to do is take the "text" that is in the comment and extract
>it to its own cell.
>
>For example, if Cell B1 has "verify address" as its comment, we would
>like the value of cell C1 to be _verify_address_. Simply put, we want
>to take the information that is in the comment and place that in its own
>cell.
>
>Is there an easy way to do this?
>
>Thank you in advance!!
>
>~Brock