Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2013
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lengthy comments within EXCEL

    Many of the comments within my Excel spreadsheet are very lengthy. So I would like to know if there is a VBA macro that can be used to not only find a given string within the comments of all cells within a spreadsheet (which is done by Excel itself), but moreover, point to the line within the comment where the string is found.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,488
    Thanks
    376
    Thanked 1,472 Times in 1,340 Posts
    Dan,

    Here's a little something to get you started:
    Code:
    Option Explicit
    
    Sub CommentSearch()
    
       Dim x As Comment
       
       For Each x In ActiveSheet.Comments
          
          Debug.Print x.Text
          Debug.Print x.Parent.Address
          Debug.Print x.Visible
          
       Next x
       
    End Sub
    CommentMacroVBA.JPG


    Not just add some testing:
    CommentMacroVBA.JPG

    Note the first set of responses was from the previous run before I added a second comment to the worksheet and the If testing to the macro.

    HTH
    Last edited by RetiredGeek; 2017-01-11 at 14:26.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2017-01-11)

  4. #3
    Lounger
    Join Date
    Jun 2013
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG:
    Thanks - looking forward to testing it.
    Dan

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,686
    Thanks
    121
    Thanked 667 Times in 608 Posts
    Very nice RG!

Posting Permissions

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