Nov 26 2021 01:42 AM
Hello,
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).
Thank you!
Nov 26 2021 03:16 AM
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" & r).AddComment Text:=Range("L" & r).Value & " " & _
Range("M" & r).Value & " " & Range("N" & r).Value
Next r
End Sub
Oct 27 2022 02:49 AM
@Hans Vogelaar Hans I looked absolutely everywhere for this. Thank you so very much for Charing your knowledge. This VBA formula also create notes for blank cells, how can I avoid or ignore blank cells and only make notes where there are "*"
If you can also share a VBA formula to use xlookup and then copy and paste exciting notes in a range
Oct 27 2022 03:16 AM
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" & r).Value <> "" Then
Range("B" & r).AddComment Text:=Range("L" & r).Value & " " & _
Range("M" & r).Value & " " & Range("N" & r).Value
End If
Next r
End Sub
Oct 27 2022 03:16 AM
Can you explain your second question (about XLOOKUP) in more detail?
Oct 27 2022 04:46 AM
Oct 27 2022 05:29 AM
Oct 27 2022 08:31 AM
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" & Rows.Count).End(xlUp).Row
For r = 2 To m
v = Evaluate("MATCH(1,(I:I=B" & r & ")*(J:J=C" & r & ")*(K:K=D" & r & "),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
Oct 27 2022 11:19 AM