Macro to Insert Comments based on Cell Values

[nitesh123]

I am a beginner to VBA, actually just learning by googling and recording macros then modifying them, but im stuck over here.

It seems like such a simple problem, but arrghhh..its driving me crazy!!!

Ok..here goes...

I have a table containing 300 rows and about 30 Columns. It is a typical cash flow file in excel.

Each column has a total in the end. What i need is to insert comments on the cell containing these totals. The comment itself should include the description of the item and the amount, see below example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Item[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Bananas[/TD]
[TD]-
[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Travel[/TD]
[TD]Taxi[/TD]
[TD]10[/TD]
[TD]-[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Spinach[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Travel[/TD]
[TD]Taxi[/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Apples[/TD]
[TD]15[/TD]
[TD]10[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Oranges[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Veggies[/TD]
[TD]Carrots[/TD]
[TD]-[/TD]
[TD]20[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Mangoes[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD][/TD]
[TD]35[/TD]
[TD]60[/TD]
[TD]35[/TD]
[/TR]
</tbody>[/TABLE]

Now, on the cell April - Total, i need to insert a comment saying

"Travel - Taxi - 10
Travel - Taxi - 5
Fruits - Apples - 15
Fruits - Oranges - 5"

The cell May - Total should have a comment displaying this

"Fruits - Bananas - 15
Veggies - Spinach -5
Fruits - Apples - 10
Fruits - Oranges - 10
Veggies - Carrot - 20"

And so on and so forth. Like i said, the actual excel has over 300 rows and 20 columns out of which several items are zero in value, so doing this manually is absurd. Right now i am filtering all columns to remove the non-zero items, copying the text and manually entering the data as comment because i can not copy-paste to a comment.

There was a similar add-on that used to accomplish this in previous (<2003) excel versions called MoreFunc. Unfortunately they don't have this for the newer versions. I am currently using 2013.

Any help on this would be greatly appreciated.

 

[My Aswer Is This]

So you want the script to look at all cells in column B F and T
And if there is any value in these cells you want that value now entered into a comment in column A

So what happens if there is a value in B14 F14 and T14

So now you want three comments entered into column A comment?

And what happens next time you run the script?

 

[AC PORTA VIA]

So you want the script to look at all cells in column B F and T
And if there is any value in these cells you want that value now entered into a comment in column A-yes

So what happens if there is a value in B14 F14 and T14-use all 3 values for comment

So now you want three comments entered into column A comment? no , 1 comment from all 3 values or from 1 or 2 or no comment if there is nothing in B, F or T

And what happens next time you run the script? - delete previous comment
Thanks for your help

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[AC PORTA VIA]

This is close what I need
the only difference is my columns to get data for comment are not next to each other -in this example below columns are 13-15
i may need 13-15 skip next column or two and get data from column 17
”Sub AddComment() Dim c As Long Dim r As Long
Dim Txt As String
On Error Resume Next
With ActiveSheet
For r = 2 To 10
Txt = ""
For c = 13 To 15
If .Cells(r, c).Value > 0 Then
If Txt = "" Then
Txt = .Cells(1, c).Value & " $" & .Cells(r, c).Value
Else
Txt = Txt & Chr(10) & .Cells(1, c).Value & " $" & .Cells(r, c).Value
End If
End If
Next c
With .Cells(r, 16)
.AddComment
.Comment.Text Text:=Txt
.Comment.Shape.TextFrame.AutoSize = True
End With
If .Cells(r, 13).Value = "" Then
If .Cells(r, 14).Value = "" Then
If .Cells(r, 15).Value = "" Then
Cells(r, 16).Comment.Delete
End If
End If
End If
Next r
End With
End Sub