Convert column into notes and apply to other columns

Question

Hello,&nbsp;&nbsp;I have a curiosity. Can I convert some column into notes and apply those notes to other column?For example, how do I convert the last three column into notes and apply them to the Injury Location (Just a sample, not related to the actual information in the excel).&nbsp;Thank you!&nbsp;

hansvogelaar · Answer

Daniela33&nbsp;
You could run a macro like this:
Sub CreateComments()
    Dim r As Long
    Dim m As Long
    m = Range("L:N").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = 2 To m
        Range("B" &amp; r).AddComment Text:=Range("L" &amp; r).Value &amp; " " &amp; _
            Range("M" &amp; r).Value &amp; " " &amp; Range("N" &amp; r).Value
    Next r
End Sub

hansvogelaar · Answer

LizeMarie&nbsp;
Like this:
Sub CreateComments()
    Dim r As Long
    Dim m As Long
    m = Range("L:N").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = 2 To m
        If Range("B" &amp; r).Value &lt;&gt; "" Then
            Range("B" &amp; r).AddComment Text:=Range("L" &amp; r).Value &amp; " " &amp; _
                Range("M" &amp; r).Value &amp; " " &amp; Range("N" &amp; r).Value
        End If
    Next r
End Sub

hansvogelaar · Answer

LizeMarie&nbsp;
Does this work?
Sub CopyComments()
    Dim r As Long
    Dim m As Long
    Dim v As Variant
    Dim c As Long
    Application.ScreenUpdating = False
    m = Range("B" &amp; Rows.Count).End(xlUp).Row
    For r = 2 To m
        v = Evaluate("MATCH(1,(I:I=B" &amp; r &amp; ")*(J:J=C" &amp; r &amp; ")*(K:K=D" &amp; r &amp; "),0)")
        If IsNumeric(v) Then
            For c = 1 To 4
                If Not Cells(v, c + 11).Comment Is Nothing Then
                    Cells(r, c + 4).AddComment Text:=Cells(v, c + 11).Comment.Text
                End If
            Next c
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

lizemarie · Answer

HansVogelaar&nbsp; Hans I looked absolutely everywhere for this. Thank you so very much for Charing your knowledge.&nbsp; This VBA formula also create notes for blank cells, how can I avoid or ignore blank cells and only make notes where there are "*"&nbsp;If you can also share a VBA formula to use xlookup and then copy and paste exciting notes in a range&nbsp;

hansvogelaar · Answer

LizeMarie&nbsp;
Can you explain your second question (about XLOOKUP) in more detail?

Forum Discussion

Convert column into notes and apply to other columns

8 Replies

Resources