[VBA] How to add Comments to Table column using text from another column

[kcmuppet]

Hi,

I want to add comments to Table column using text from another column in the same row.

I've been trying to modify the VLookup-based solution (which only works if the values in the column to apply comments to {Col1] are unique).

Dim lookRange As Range 'Range to use for comments

'Define ranges
Set commentRange = Range("Table10[Col1]")
Set lookRange = Range("Table10[[Col1]:[Col3]]")

'loop through and add comment
For Each c In commentRange
    With c
        .ClearComments
        .AddComment
        .Comment.Text Text:=CStr(WorksheetFunction.VLookup(c.Value, lookRange, 3, False))
    End With
Next c

End Sub

Should I be using listobjects along the lines of For c = 1 To tbl.ListRows.Count?

 

[kcmuppet]

This seems to work, by simply specifying the number of columns offset from the active cell (in my case 2):

Sub AddNotesfromColumn()
Dim commentRange As Range 'Range to add comments to
Dim c As Range
Set commentRange = Range("Table10[Col1]") 'Define range

'loop through and add comment
For Each c In commentRange
 With c
 .ClearComments
 .AddComment
 .Comment.Text Text:=c.Offset(0, 2).Value
End With
Next c

End Sub