+ Reply to Thread
Results 1 to 3 of 3

How to Extract Cell "Comments"

Hybrid View

  1. #1
    Registered User
    Join Date
    07-18-2007
    Posts
    3

    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.

  2. #2
    Registered User
    Join Date
    07-18-2007
    Posts
    3

    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.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1