Use VB Script to systematically add information to a cell comment

I have a spreadsheet that is systematically updating cells from another source.  The cell contents gets populated with an Item#.  However, there is a lot of other information that needs to be associated with the cell, so I add all the rest of the info on to the cell comment.  It works great as long as there is only one entry per cell.

However, it is possible for the macro to comes across the cell a second time. And, I need it to append the new information to the original information that was already stored in the cell comment.  Is it possible to write something like:

Comnt = "Item Info: " & Chr(10) & Chr(13) & _
        "SKU: " & SKU2 & Chr(10) & Chr(13) & _
        "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
        "Qty: " & Qty2 & Chr(10) & Chr(13) & _
        "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13) & objComment

Open in new window


the second time around?  Below is the code used to populate the cell the first time around.  Thanks!

Sheets("INVENTORY").Cells(y, x).Interior.Color = vbBlue
Sheets("INVENTORY").Cells(y, x).Value = SKU2

Comnt = "Item Info: " & Chr(10) & Chr(13) & _
        "SKU: " & SKU2 & Chr(10) & Chr(13) & _
        "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
        "Qty: " & Qty2 & Chr(10) & Chr(13) & _
        "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
        "Location Info: " & Chr(10) & Chr(13) & _
        "Loc#: " & LocNum2 & Chr(10) & Chr(13) & _
        "AREA: " & s_AREA & Chr(10) & Chr(13) & _
        "WAREA: " & s_WAREA & Chr(10) & Chr(13) & _
        "WZONE: " & s_WZONE & Chr(10) & Chr(13) & _
        "Capacity: " & s_CAPACITY & Chr(10) & Chr(13) & _
        "Velocity: " & s_VELOCITY & Chr(10) & Chr(13) & _
        "Trav Seq: " & s_TRAVSEQ
        
ComLen4 = Len("Item Info: " & Chr(10) & Chr(13) & _
             "SKU: " & SKU2 & Chr(10) & Chr(13) & _
             "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
             "Qty: " & Qty2 & Chr(10) & Chr(13) & _
             "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13))
        
With Sheets("INVENTORY").Cells(y, x)
    Set objComment = Sheets("INVENTORY").Cells(y, x).AddComment(Comnt)
    With objComment.Shape.TextFrame
        .Characters.Font.Size = 16
        .Characters.Font.Bold = False
        .Characters(1, 11).Font.Bold = True
        .Characters(1, 11).Font.Underline = True
        .Characters(ComLen4, 15).Font.Bold = True
        .Characters(ComLen4, 15).Font.Underline = True
    End With
End With

Sheets("INVENTORY").Cells(y, x).Comment.Visible = False
Sheets("INVENTORY").Cells(y, x).Comment.Shape.TextFrame.AutoSize = True

Open in new window

jmccloskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
Hi, this should work.  I've added to check to see whether the comment exists or not.

Regards,

Rob.

Sheets("INVENTORY").Cells(y, x).Interior.Color = vbBlue
Sheets("INVENTORY").Cells(y, x).Value = SKU2

Comnt = "Item Info: " & Chr(10) & Chr(13) & _
        "SKU: " & SKU2 & Chr(10) & Chr(13) & _
        "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
        "Qty: " & Qty2 & Chr(10) & Chr(13) & _
        "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
        "Location Info: " & Chr(10) & Chr(13) & _
        "Loc#: " & LocNum2 & Chr(10) & Chr(13) & _
        "AREA: " & s_AREA & Chr(10) & Chr(13) & _
        "WAREA: " & s_WAREA & Chr(10) & Chr(13) & _
        "WZONE: " & s_WZONE & Chr(10) & Chr(13) & _
        "Capacity: " & s_CAPACITY & Chr(10) & Chr(13) & _
        "Velocity: " & s_VELOCITY & Chr(10) & Chr(13) & _
        "Trav Seq: " & s_TRAVSEQ
        
ComLen4 = Len("Item Info: " & Chr(10) & Chr(13) & _
             "SKU: " & SKU2 & Chr(10) & Chr(13) & _
             "Lot#: " & Lot2 & Chr(10) & Chr(13) & _
             "Qty: " & Qty2 & Chr(10) & Chr(13) & _
             "LPN: " & LPN2 & Chr(10) & Chr(13) & Chr(10) & Chr(13))
        
With Sheets("INVENTORY").Cells(y, x)
	If TypeName(Sheets("INVENTORY").Cells(y, x).Comment) = "Comment" Then
		Sheets("INVENTORY").Cells(y, x).Comment.Text Sheets("INVENTORY").Cells(y, x).Comment.Text & Chr(10) & Chr(13) & Comnt
	Else
	    Set objComment = Sheets("INVENTORY").Cells(y, x).AddComment(Comnt)
	    With objComment.Shape.TextFrame
	        .Characters.Font.Size = 16
	        .Characters.Font.Bold = False
	        .Characters(1, 11).Font.Bold = True
	        .Characters(1, 11).Font.Underline = True
	        .Characters(ComLen4, 15).Font.Bold = True
	        .Characters(ComLen4, 15).Font.Underline = True
	    End With
	End If
End With

Sheets("INVENTORY").Cells(y, x).Comment.Visible = False
Sheets("INVENTORY").Cells(y, x).Comment.Shape.TextFrame.AutoSize = True

Open in new window

0
jmccloskAuthor Commented:
Hi Rob,

Unfortunately, this did not work.  For some reason, it never gets to this statement when it encounters a duplicate:

If TypeName(Sheets("INVENTORY").Cells(y, x).Comment) = "Comment" Then
		Sheets("INVENTORY").Cells(y, x).Comment.Text Sheets("INVENTORY").Cells(y, x).Comment.Text & Chr(10) & Chr(13) & Comnt

Open in new window


I have 10 lines of data in my test file.  Six of them are non-duplicates.  The other four lines are two sets of duplicates.  When it handles the six non-duplicates, it resolves correctly in the 'Else' part of the statement.  And, when it encounters the first record of a duplicate set, it also correctly resolves using the 'Else' part of the statement.  However, when it gets to the last two records, which are duplicates, it does not handle them at all with this If-Then.  It skips the statement completely.

I put two MsgBox statements in the If-Then.  One on the 'If' part and the second on the 'Else' part.  I get the 'Else' message only eight times.  And, I get the 'If' message zero times.
0
RobSampsonCommented:
Hmmm, instead of this
If TypeName(Sheets("INVENTORY").Cells(y, x).Comment) = "Comment" Then

Open in new window


Try this
If Sheets("INVENTORY").Cells(y, x).HasComment = True Then

Open in new window


Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

jmccloskAuthor Commented:
Got this error on that line.If statement error
0
RobSampsonCommented:
Ok try
If Not Sheets("INVENTORY").Cells(y, x).Comment Is Nothing Then

Open in new window

0
jmccloskAuthor Commented:
This second suggestion did end up working.  I had another error in my code that was masking its success that I had to work through.  Once I resolved that, this statement works perfectly!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.