Sep 19 2022 12:09 AM
Hi dear all,
I have a long text in cells in a table (see file, column R). I rather have the text from the cell R5 in the comment/note of cell P5.
Is this possible? And if yes, how do I do it?
Sep 19 2022 03:24 AM
SolutionTry this macro:
Sub MoveTextToComments()
Dim w As Worksheet
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
Set w = ActiveSheet
' Find last used row
m = w.Range("R" & w.Rows.Count).End(xlUp).Row
' Remove any existing comments from column P
w.Range("P5:P" & m).ClearComments
' Loop through the rows
For r = 5 To m
' Add comment to cell in column P
w.Range("P" & r).AddComment Text:=w.Range("R" & r).Text
Next r
' Optional: clear column R
'w.Range("R5:R" & m).ClearContents
Application.ScreenUpdating = True
End Sub
Sep 19 2022 04:32 AM
Sep 19 2022 05:25 AM
The result might no be what you wanted: change
w.Range("P" & r).AddComment Text:=w.Range("R" & r).Text
to
w.Range("P" & r).AddComment(Text:=w.Range("R" & r).Text).Shape.TextFrame.AutoSize = True
Sep 19 2022 07:41 AM
Sep 19 2022 07:50 AM
I don't think that would be easy - you'd have to break up the text from the cell into separate lines. Sorry!
Sep 19 2022 03:24 AM
SolutionTry this macro:
Sub MoveTextToComments()
Dim w As Worksheet
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
Set w = ActiveSheet
' Find last used row
m = w.Range("R" & w.Rows.Count).End(xlUp).Row
' Remove any existing comments from column P
w.Range("P5:P" & m).ClearComments
' Loop through the rows
For r = 5 To m
' Add comment to cell in column P
w.Range("P" & r).AddComment Text:=w.Range("R" & r).Text
Next r
' Optional: clear column R
'w.Range("R5:R" & m).ClearContents
Application.ScreenUpdating = True
End Sub