Inserting comment with cell reference

[Lavan]

Hello Experts,



I need a macro to insert the comments with cell reference.

For example, I have spread sheet with huge data like below... I need to add the value of a cell in column H to the cell in column A, as comment only if cell in column G is not blank. once the macro is run A3 should have comment ""Total:200"" and A5 should have comment ""Total:250"". Any help is greatly appriciated.



Thanks in Advance


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Name</td><td style="text-align: center;;">Age</td><td style="text-align: center;;">Gender</td><td style="text-align: center;;">Address</td><td style="text-align: center;;">Phone</td><td style="text-align: center;;">Cell</td><td style="text-align: center;;">Score</td><td style="text-align: center;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Andrew</td><td style="text-align: center;;">36</td><td style="text-align: center;;">M</td><td style="text-align: center;;">xyz</td><td style="text-align: center;;">123</td><td style="text-align: center;;">972</td><td style="text-align: center;;"></td><td style="text-align: center;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Solmon</td><td style="text-align: center;;">43</td><td style="text-align: center;;">M</td><td style="text-align: center;;">abc</td><td style="text-align: center;;">123</td><td style="text-align: center;;">972</td><td style="text-align: center;;">45</td><td style="text-align: center;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Chris</td><td style="text-align: center;;">33</td><td style="text-align: center;;">M</td><td style="text-align: center;;">123</td><td style="text-align: center;;">123</td><td style="text-align: center;;">972</td><td style="text-align: center;;"></td><td style="text-align: center;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">cathy</td><td style="text-align: center;;">45</td><td style="text-align: center;;">F</td><td style="text-align: center;;">456</td><td style="text-align: center;;">123</td><td style="text-align: center;;">972</td><td style="text-align: center;;">65</td><td style="text-align: center;;">250</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">linda</td><td style="text-align: center;;">27</td><td style="text-align: center;;">F</td><td style="text-align: center;;">syz</td><td style="text-align: center;;">123</td><td style="text-align: center;;">972</td><td style="text-align: center;;"></td><td style="text-align: center;;">225</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[VoG]

Hi. Try this

Sub AddCmt()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Range("G" & i).Value <> "" Then Range("A" & i).NoteText Text:="Total: " & Range("H" & i).Value
Next i
End Sub

 

[njimack]

Sub Add_Comment()
Dim Last_Row As Long, n As Long

With Sheets("Sheet1")
    Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
    
    For n = 2 To Last_Row
    
        If .Cells(n, 7).Value <> "" Then
            .Cells(n, 1).AddComment
            .Cells(n, 1).Comment.Text Text:="Total:" & .Cells(n, 8).Value
        End If
    Next n
End With
End Sub

 

[shg]

You can also do this with a UDF. In I2 and copy down,

=SetComment(A2, IF(G2="", "", "Total: " & H2))

Function SetComment(r As Range, sText As String) As String
    If Not r.Comment Is Nothing Then r.Comment.Delete
    If Len(sText) Then r.AddComment.Text sText
End Function

 

[Lavan]

Thank you VoG. It worked perfect.