Extract Comments and paste as values

B

Btibert

Hello all,

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

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

Is there an easy way to do this?

Thank you in advance!!

~Broc
 
G

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
 
Top