PDA

View Full Version : Why doesn't this work in a loop?



ModusPonen
03-21-2011, 12:27 PM
I'm using the following code to add comments to cells, or add to the existing comments in cells. For some reason, it will work fine the first time through, but not the second. Any ideas?

Sub CommentTest()
Dim HasComment As Boolean
Dim oldCmtStr As String
Dim noteStr As String

noteStr = "hey there"

On Error Resume Next
HasCommnet = ActiveCell.Comment.Parent.Address = ActiveCell.Address
On Error GoTo 0
If HasComment = True Then
oldCmtStr = ActiveCell.Comment.Text
ActiveCell.Comment.Text Text:=oldCmtStr & Chr(10) & noteStr
Else
ActiveCell.AddComment Text:=noteStr
End If

End Sub

mdmackillop
03-21-2011, 12:39 PM
Welcome to VBAX

Add this line before your code
Option Explicit

ModusPonen
03-21-2011, 01:11 PM
Thanks so much for the reply. Now however, it tells me the variable is not defined.

mdmackillop
03-21-2011, 01:13 PM
That is the purpose of Option Explicit. Check your spelling.

Kenneth Hobs
03-21-2011, 01:16 PM
Sub CommentTest()
Dim CommentText As String
Dim oldCmtStr As String
Dim noteStr As String

noteStr = "hey there"

On Error Resume Next
CommentText = Empty
CommentText = ActiveCell.Comment.Text
On Error GoTo 0

If CommentText <> Empty Then
oldCmtStr = ActiveCell.Comment.Text
ActiveCell.Comment.Text Text:=oldCmtStr & Chr(10) & noteStr
Else
ActiveCell.AddComment Text:=noteStr
End If

End Sub

ModusPonen
03-21-2011, 01:17 PM
One was "HasCommnet" and the other was "HasComment". Thanks again.