Putting Cell data in a comment via VBA

[rpadminserver]

Some back ground: I have a sheet called look up. This sheet is populated via an advance filter macro, so this sheet constantly refreshing when the user selects a new code to look up.

One of the fields that is displayed is a condition code, the details for this condition code is located in another sheet call CONDITION.

Here is what I'm trying to do:
IS there a way to put the details of this specific condition in a COMMENT.

So for example
After my advance filter runs I have 4 records displayed
The 4 records EACH have a different condtion code
Column j (on the work sheet that has the advanced filter)
111
222
333
444

So on my CONDITION sheet in column A is the CODE and column B has the description and column c has a note.
A B c
111 Not Ready Note 1
222 Ready Note 2
333 Willing Note 3
444 Able Note 4

What I need is a comment to be created for each cell in column j that contains the data in Columns b and c on the code sheet, the key being matching Column J with Column A.

Sorry, I hope I worded this a way that you under stand
Thanks everone!

 

[Krishnakumar]

Hi,

Try something like this

Sub kTest()
Dim i           As Long
Dim rngComment  As Range
Dim dic         As Object
Dim rngData     As Range
Dim ka, k

Set rngComment = Worksheets("Condition").Range("a2:c5") 'adjust to suit

Set dic = CreateObject("scripting.dictionary")
    dic.comparemode = 1
ka = rngComment

For i = 1 To rngComment.Rows.Count
    dic.Item(ka(i, 1)) = rngComment.Cells(i, 2) & Chr(10) & rngComment.Cells(i, 3)
Next

With Sheets("Look up") '<< adjust to suit
    Set rngData = Intersect(.UsedRange, .Columns("j:j"))
End With

k = rngData

On Error Resume Next
rngData.ClearComments
On Error GoTo 0
For i = 1 To UBound(k, 1)
    If dic.exists(k(i, 1)) Then
        rngData.Cells(i, 1).AddComment dic.Item(k(i, 1))
    End If
Next

End Sub

Note: adjust the ranges

 

[rpadminserver]

Thank you!!!!!!