How to Extract Cell "Comments"

I have a large worksheet with each cell containing "comments". I used the "Insert Comment" feature after 'right-clicking' on the individual cell. I am adding multiple lines of free format text as "Comments". Is there a way to programatically extract those comments ?

For example, the first column of every row contains the name of a financial institution. In the other 6 columns, I have certain lending considerations. In column #2 I have considerations (in the form of comments) for "member eligibility". Is there a way for me to write some script that would extract the comments for column #2 for each row and write it out to a table and/or simple report ?

See sample "desired" report below...the 1st column of my worksheet is always a Bank name and the 2nd column contains 0, 1, or more comments about "member eligibility". The 3rd column contains comments about "approval elgibility", and so on. I want to be able to extract the cell comments from column 2 and display them with the Bank Name. I want to be able to extract the cell comments from column 3 and display them with the Bank Name in a second report, etc.

MEMBER ELIBILITY REPORT
ABC Bank
Applicant must be a member
Loan must be co-signed
DEF Bank
Loan must be co-signed
Co-signer must be a member
Applicant and co-signer must be members for 6 months minimum

APPROVAL ELIBILITY REPORT
ABC Bank
XXXXXXXXXXXX
XXXXXXXXXXXX
DEF Bank
XXXXXXXXXXXX
XXXXXXXXXXXX

Basically, are the comments attached to a cell "extractable" into any kind of format for a report ? THANKS -- Charlie O.

I have a large worksheet with each cell containing "comments". I used the "Insert Comment" feature after 'right-clicking' on the individual cell. I am adding multiple lines of free format text as "Comments". Is there a way to programatically extract those comments ?

For example, the first column of every row contains the name of a financial institution. In the other 6 columns, I have certain lending considerations. In column #2 I have considerations (in the form of comments) for "member eligibility". Is there a way for me to write some script that would extract the comments for column #2 for each row and write it out to a table and/or simple report ?

See sample "desired" report below...the 1st column of my worksheet is always a Bank name and the 2nd column contains 0, 1, or more comments about "member eligibility". The 3rd column contains comments about "approval elgibility", and so on. I want to be able to extract the cell comments from column 2 and display them with the Bank Name. I want to be able to extract the cell comments from column 3 and display them with the Bank Name in a second report, etc.

MEMBER ELIBILITY REPORT
ABC Bank
Applicant must be a member
Loan must be co-signed
DEF Bank
Loan must be co-signed
Co-signer must be a member
Applicant and co-signer must be members for 6 months minimum

APPROVAL ELIBILITY REPORT
ABC Bank
XXXXXXXXXXXX
XXXXXXXXXXXX
DEF Bank
XXXXXXXXXXXX
XXXXXXXXXXXX

Basically, are the comments attached to a cell "extractable" into any kind of format for a report ? THANKS -- Charlie O.

Maybe like this:

Sub ListComments()
    Dim wksSrc  As Worksheet
    Dim wksCom  As Worksheet
    Dim rCom    As Excel.Range
    Dim rInp    As Excel.Range
    Dim rOut    As Excel.Range

    Set wksSrc = ActiveSheet

    On Error Resume Next
    Set wksCom = Worksheets("Comments")
    If Err Then
        Set wksCom = Worksheets.Add(After:=Sheets(Sheets.Count))
        wks.Name = "Comments"
    End If
    wksCom.Cells.ClearContents

    Set rCom = wksSrc.Cells.SpecialCells(xlCellTypeComments)
    If rCom Is Nothing Then Exit Sub
    On Error GoTo 0

    Set rOut = wksCom.Range("A1")
    For Each rInp In rCom
        rOut(1, 1) = rInp.Address(False, False)
        rOut(1, 2) = rInp.Comment.Text
        Set rOut = rOut.Offset(1)
    Next rInp
End Sub