VBA code to lookup cell value and give a description as a comment

[NSegna]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unit
[/TD]
[TD]Qty
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Unit
[/TD]
[TD]Desc
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TR-010
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TR-010
[/TD]
[TD]Trenching in Roadway
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TR-030
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD]TR-020
[/TD]
[TD]Trenching in Sidewalk
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TR-030
[/TD]
[TD]Trenching in Grass
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hello,

I was wondering if there is a vba code that can vlookup the value of a cell and return a description as a comment. See above for an example.
I want the comment in cell A2 to be F2 and the comment in A3 to be F4.

Thanks
[TABLE="width: 161"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Hello,

I was wondering if there is a vba code that can vlookup the value of a cell and return a description as a comment. See above for an example.
I want the comment in cell A2 to be F2 and the comment in A3 to be F4.

Thanks

Try this

Sub add_comment()
    Dim c As Range, f As Range
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Set f = Range("E:E").Find(c, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            On Error Resume Next
            c.Comment.Shape.Delete
            On Error GoTo 0
            c.AddComment
            c.Comment.Visible = True
            c.Comment.Text Text:=f.Offset(, 1).Value
        End If
    Next
End Sub

 

[NSegna]

Thanks for responding to my post.
The code works and does display the text as a comment. Is there a way to make the comment only appear when the cell that has the comment is clicked on or hovered over? Also if the descriptions for the units are on another sheet, how and where would I put the sheet name?

Thanks

 

[DanteAmor]

Thanks for responding to my post.
The code works and does display the text as a comment. Is there a way to make the comment only appear when the cell that has the comment is clicked on or hovered over? Also if the descriptions for the units are on another sheet, how and where would I put the sheet name?

Thanks

Try this.
Change Sheet3 by the name of the sheet with the descriptions

Sub add_comment()
    Dim c As Range, f As Range
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        Set f = Sheets("[COLOR=#ff0000][B]Sheet3[/B][/COLOR]").Range("E:E").Find(c, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            On Error Resume Next
            c.Comment.Shape.Delete
            On Error GoTo 0
            c.AddComment
            c.Comment.Visible = False
            c.Comment.Text Text:=f.Offset(, 1).Value
        End If
    Next
End Sub