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.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Rng As Range
    Dim c As Long
    Dim r As Long
    Dim Txt As String
    With ActiveSheet
        Set Rng = .Range("A1").CurrentRegion
        With Rng
            For c = 3 To .Columns.Count
                Txt = ""
                For r = 2 To .Rows.Count - 1
                    If .Cells(r, c).Value > 0 Then
                        If Txt = "" Then
                            Txt = .Cells(r, 1).Value & " - " & .Cells(r, 2).Value & " - " & .Cells(r, c).Value
                        Else
                            Txt = Txt & Chr(10) & .Cells(r, 1).Value & " - " & .Cells(r, 2).Value & " - " & .Cells(r, c).Value
                        End If
                    End If
                Next r
                With .Cells(.Rows.Count, c)
                    .AddComment
                    .Comment.Text Text:=Txt
                End With
            Next c
        End With
    End With
End Sub

 

[mole999]

the basic comment could be Range("F28").Comment.Text Text:="Automated:" & Chr(10) & F2 & " " & C5 & " " & D4

how or why you actually need to load that into a hidden comment is beyond me, have you considered a Pivot Table?

 

[nitesh123]

Wow Andrew, thanks alot!! This is awesome and worked perfectly! I tried to understand the code and shall apply it now to my actual file.

Thanks again, i really appreciate it.

Try:

Sub Test()
    Dim Rng As Range
    Dim c As Long
    Dim r As Long
    Dim Txt As String
    With ActiveSheet
        Set Rng = .Range("A1").CurrentRegion
        With Rng
            For c = 3 To .Columns.Count
                Txt = ""
                For r = 2 To .Rows.Count - 1
                    If .Cells(r, c).Value > 0 Then
                        If Txt = "" Then
                            Txt = .Cells(r, 1).Value & " - " & .Cells(r, 2).Value & " - " & .Cells(r, c).Value
                        Else
                            Txt = Txt & Chr(10) & .Cells(r, 1).Value & " - " & .Cells(r, 2).Value & " - " & .Cells(r, c).Value
                        End If
                    End If
                Next r
                With .Cells(.Rows.Count, c)
                    .AddComment
                    .Comment.Text Text:=Txt
                End With
            Next c
        End With
    End With
End Sub

 

[nitesh123]

Hi Andrew,

I managed to make it successfully work. I also included a macro to auto-resize the comments. I have one question regarding the formatting though. I would like the display to have $10.00 or $ (10.00) instead of just 10. How can i do this?

 

[mole999]

add a custom format from the cell format (probably accounting to start with)

 

[nitesh123]

Hi Mole999,

The formatting needs to be done to the digits that are within the comment that was crated using the macro Andrew suggested. Cell format will not serve the purpose.

add a custom format from the cell format (probably accounting to start with)

 

[Andrew Poulsom]

If you format the cells you can try:

If Txt = "" Then
    Txt = .Cells(r, 1).Text & " - " & .Cells(r, 2).Text & " - " & .Cells(r, c).Text
Else
    Txt = Txt & Chr(10) & .Cells(r, 1).Text & " - " & .Cells(r, 2).Text & " - " & .Cells(r, c).Text
End If

 

[NeverTooOld]

I have a similar question. I have tried to modify the code above, but was not successful.

I have a spreadsheet of 5000 employees (5000 rows) that lists historical salary values in each row for 2013, 2014 and 2015 in columns M, N and O respectively.

I want to add a comment in column P of each row, that summarizes the data in columns M, N and O. Something like this:
2013 $1000
2014 $2000
2015 $3000

Ideally, the comment would be added automatically when data is entered into the row.

I have a separate routine to open a form and enter a new employee. I would like the "add" routine to add the new employee to the spreadsheet and add the comment at the same time.

Can you help me?

Thank you!

 

[zor00]

You could try this
Code:

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

I have a similar question. I have tried to modify the code above, but was not successful.

I have a spreadsheet of 5000 employees (5000 rows) that lists historical salary values in each row for 2013, 2014 and 2015 in columns M, N and O respectively.

I want to add a comment in column P of each row, that summarizes the data in columns M, N and O. Something like this:
2013 $1000
2014 $2000
2015 $3000

Ideally, the comment would be added automatically when data is entered into the row.

I have a separate routine to open a form and enter a new employee. I would like the "add" routine to add the new employee to the spreadsheet and add the comment at the same time.

Can you help me?

Thank you!