How to put comment in each column (VBA inserted)

[tamrob23]

Good Afternoon,

I have a VBA that needs a small tweek. The VBA that is capturing 4 columns and right now all the comments are going into one column. Basically what ever comments that are in columns A,B,C,D,E need to go into columns F,G,H,I,J. Thank you for taking time to read my thread and have a great day!



Function Comment(ByVal incell As Range) As String
On Error GoTo A:
Comment = incell.Comment.Text
Exit Function
A:
Comment = "Doesn't Exist"
End Function

Sub All_Comments()
Dim myrange As Range, myarray() As String
Set myrange = Range("A1:E100")
For Each cell In myrange
If Not Comment(cell) = "Doesn't Exist" Then
k = k + 1
ReDim Preserve myarray(1 To k) As String
myarray(k) = Comment(cell)
End If
Next

For i = 1 To UBound(myarray)
Range("F1").Offset(i - 1) = myarray(i)
Next i
End Sub

 

[Leith Ross]

Hello tamrob23,

This will copy the comments from "A1:E100" to "F1:J100".

Sub CopyAllComments()


    Dim Cell    As Range
    Dim Rng     As Range
    Dim Text    As String
    
        Set Rng = Worksheets("Sheet1").Range("A1:E100")
        
        For Each Cell In Rng
            On Error Resume Next
                Text = Cell.Comment.Text
                If Err = 0 Then
                    Cell.Offset(0, 5).Comment.Delete
                    Cell.Offset(0, 5).AddComment Text
                End If
            On Error GoTo 0
        Next Cell
        
End Sub

 

[tamrob23]

Good Morning,

Thank you for your response, but the VBA you provided moved the comments but they are not extracted from the cell. I would like for the comments to me shown so I can see what the person commented.

 

[MickG]

Perhaps this:-

[COLOR="Navy"]Sub[/COLOR] MG21Sep35
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, t, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:E100")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dn.Comment [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 5) = Dn.Comment.Text
    [COLOR="Navy"]Else[/COLOR]
        Dn.Offset(, 5).Value = ""
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[tamrob23]

Yaaaaaay! This is exactly what I need - thank you both so much for taking time out to help with my VBA, have a great day!

 

[MickG]

You're welcome