[ms office - 2003] excel macro

[browolf]

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?

[CESIL]

Do you want the count in the same cell?

[browolf]

Do you want the count in the same cell?

 

yes, same cell the comment is attached to

[CESIL]

Try this...select the cell with comment and then Alt+F8 and run countLines

 

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...

Edited February 15, 2011 by CESIL

[CESIL]

This version will work even if the cell has no comment attached

 

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