This code should help...
Sub addcomments()
Dim lastrow As Long
Dim i As Long
Dim ws As Worksheet
Dim ws_rebate As Worksheet
Set ws = Worksheets("D Data") 'the sheet getting the comments, change as needed
Set ws_rebate = Worksheets("Rebate Info") 'the source of the comment content.
lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row 'finds last row of data in Column A, change the "A" if needed
On Error Resume Next 'if vlookup fails just go to next line
For i = 3 To lastrow 'starts at 3 as per example book, if data starts on a different row, adjust
ws.Range("AE" & i).ClearComments 'removes old comment in cell
'in the line below AE gets the comment, by looking up column A value in the rebate sheet in the range A2 to B9. Change A2:B9 as needed.
ws.Range("AE" & i).AddComment WorksheetFunction.VLookup(Sheets(1).Range("A" & i).Value, Sheets(2).Range("A2:B9"), 2, False)
Next i
On Error GoTo 0 'resume normal error conditions
End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks