+ Post New Thread
Results 1 to 5 of 5
Office Software Thread, excel macro in Technical; anyone a genius at excel vb? who can whip up a macro that reads a cell, reads the comment, counts ...
  1. #1
    browolf's Avatar
    Join Date
    Jun 2005
    Location
    Mars
    Posts
    1,527
    Thank Post
    107
    Thanked 89 Times in 75 Posts
    Blog Entries
    46
    Rep Power
    40

    excel macro

    anyone a genius at excel vb?

    who can whip up a macro that reads a cell, reads the comment, counts the number of lines in the comment, then posts the result in the cell?

  2. #2

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Do you want the count in the same cell?

  3. #3
    browolf's Avatar
    Join Date
    Jun 2005
    Location
    Mars
    Posts
    1,527
    Thank Post
    107
    Thanked 89 Times in 75 Posts
    Blog Entries
    46
    Rep Power
    40
    Quote Originally Posted by CESIL View Post
    Do you want the count in the same cell?
    yes, same cell the comment is attached to

  4. #4

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Try this...select the cell with comment and then Alt+F8 and run countLines

    Code:
    Public Sub countLines()
    
    Dim vMyArray
    
    vMyArray = Split(ActiveCell.Comment.Text, Chr(10))
    
    ActiveCell.Value = UBound(vMyArray) - LBound(vMyArray) + 1
    
    End Sub
    EDIT: Sorry should have said... Alt+F11 double click on ThisWorkbook and paste the code. Then close VBA and try the above...
    Last edited by CESIL; 15th February 2011 at 07:27 PM.

  5. #5

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    This version will work even if the cell has no comment attached

    Code:
    Public Sub countLines()
    
    Dim vMyArray
    
    On Error GoTo noComment
    
    If Not ActiveCell.Comment.Text = "" Then
    
        vMyArray = Split(ActiveCell.Comment.Text, Chr(10))
    
        ActiveCell.Value = UBound(vMyArray) - LBound(vMyArray) + 1
        
        Exit Sub
        
    noComment:
        MsgBox "No comment found"
    
    End If
    
    End Sub

  6. Thanks to CESIL from:

    browolf (15th February 2011)

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2003] First pesty macro in Excel. Trolling me hard.
    By El_Nombre in forum Office Software
    Replies: 7
    Last Post: 28th January 2011, 01:13 PM
  2. Assigning a excel Macro to work in all workbooks
    By FatBoy in forum Office Software
    Replies: 8
    Last Post: 24th November 2010, 02:23 PM
  3. Macro + Excel 2007 + SIMS
    By SteveB in forum MIS Systems
    Replies: 20
    Last Post: 3rd March 2010, 09:23 AM
  4. [MS Office - XP] Excel Macro - If Cell Value
    By ahuxham in forum Office Software
    Replies: 0
    Last Post: 18th December 2009, 10:27 AM
  5. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 10:27 AM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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