Copying comments to cells

[Pandab2002]

I have a simple spreadsheet that is a site directory. It has various telephone numbers and IP addresses entered as comments. There are a couple hundred of them. Is there a way to quickly copy all these comments into cells?

Any help would be appreciated.

 

[SkipVought]

Hi,

No way with native Excel functionality.

However, here's some code that you might be able to use. Copy and paste in a module in the VB Editor (alt+F11 toggles between VB Editor and Sheet)

If you have further VBA Code questions, pleas post in Forum707.

Sub Coments2Sheet()
'SkipVought - [URL unfurl="true"]www.Tek-Tips.com[/URL]
'2007 Jun 16
'transfers comments to assiciated cell in the active sheet.
    Dim cmt As Comment, r As Range
    For Each r In ActiveSheet.UsedRange
        For Each cmt In ActiveSheet.Comments
            If r.Address = cmt.Parent.Address Then
                r.Value = cmt.Text
                Exit For
            End If
        Next
    Next
End Sub

Skip,

_[red][/red]