I have some data filled out in a format similar to the below. I would like to be
ID: 3560544 • Letter: I
Question
I have some data filled out in a format similar to the below. I would like to be able to have a comment displayed on cell A2 based on the value in cell XX2 and then for cell B2 I'd like the comment to be the value from XY2. The values in column A vary and can increase and decrease in quantity so one week it may show 3 items and the next week it may show 10 so I'd like it to be scalable vertically if possible by some sort of "lastrow" option.
The data on each row changes frequently so I understand that the macro will like need to clear the results every time it is ran and then repopulate accordingly.
Is this something that is feasible?
Explanation / Answer
Try this This version tests if there's text in XX & XY and if there is adds the comments, otherwise no comment is added.
Sub HasComment()
Dim mycomment As Object, LastRow As Long
Dim c As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each c In Range("A2:A" & LastRow)
'Col A
Set mycomment = c.Comment
If mycomment Is Nothing Then
If Cells(c.Row, "XX") <> "" Then
c.AddComment
c.Comment.Text Cells(c.Row, "XX").Text
End If
Else
c.Comment.Delete
If Cells(c.Row, "XX") <> "" Then
c.AddComment
c.Comment.Text Cells(c.Row, "XX").Text
End If
End If
'Col B
Set mycomment = c.Offset(, 1).Comment
If mycomment Is Nothing Then
If Cells(c.Row, "XY") <> "" Then
c.Offset(, 1).AddComment
c.Offset(, 1).Comment.Text Cells(c.Row, "XY").Text
End If
Else
c.Offset(, 1).Comment.Delete
If Cells(c.Row, "XY") <> "" Then
c.Offset(, 1).AddComment
c.Offset(, 1).Comment.Text Cells(c.Row, "XY").Text
End If
End If
Next
End Sub