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.

 

[zor00]

In this code, I just apply for row from 2 to 10 ( For r = 2 To 10) , you can adjust for your file

 

[AC PORTA VIA]

what code would work if columns for comment are not next to each other
in other words what if columns that are used to get info for comment are not next to each other like column b, f, t

 

[My Aswer Is This]

I would suggest you start a new posting or explain in detail what your wanting. Trying to help you by reffering to a posting from 4 years ago is hard for me.

what code would work if columns for comment are not next to each other
in other words what if columns that are used to get info for comment are not next to each other like column b, f, t

 

[AC PORTA VIA]

I have same question that was somehow answered by zor00 but my columns to get info for comment are not next to each other
if i gave data in row b3, f3, t3 and want to ad that as comment in row a3

thanks for reply

 

[My Aswer Is This]

You said:

if i gave data in row b3, f3, t3 and want to ad that as comment in row a3

Excel has no row b3 or f3 or t3

Excel has ​

cells​

B3 and cells F3 and cells T3

So are you saying if you enter Cat in Range("B3") you want Cat entered into Range("A3") also

Or do you want a cell comment entered in A3
So in A3 you want the comment in A3 to be Cat


​

 

[AC PORTA VIA]

yes cells
i need code that would loop to the last row and find any value (or info from vlookup) in cell b3,f3 and t3 and add that as comment in cell a3
example
if cell b3 is apple , f3 is orange an t3 is banana comment in a3 should be
apple
orange
banana
if b4 is cat, t4 dog cell a4 comment should be
cat
dog
it should add comment only if there is anything in cells b, f or t

 

[My Aswer Is This]

See if this will do what you want:

If you enter any value in column B F or T
The column to the left of it will have a comment entered.

If this is not what you want you will need to provide more clear details.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/4/2018  7:08:10 PM  EST
If Target.Column = 2 Or Target.Column = 6 Or Target.Column = 20 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(, -1).AddComment Target.Value
End If
End Sub

 

[AC PORTA VIA]

Thank you for help but unfortunately this is no what i need
comment needs to be only in one column based on value from other cells
in my case comment needs to be in column A if there is anything in column B, F or T

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/5/2018  6:53:16 PM  EST
If Target.Column = 2 Or Target.Column = 6 Or Target.Column = 20 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As Long
ans = Target.Row
Cells(ans, 1).AddComment Target.Value
End If
End Sub

 

[AC PORTA VIA]

it has to be macro to call out since data in column B,F or T is already there