Retrieving Multiple Comments

[tamrob23]

Good Afternoon,

I wrote a VBA in Excel (below) so I can retrieve comments but I quickly realized that people are using multiple cells in multiple columns to make comments. Is there anyway I can create something that will automatically find the comments and put them in an empty cell? If I continue to use what I created below, I'm going from 8 columns to 16 (because I'm creating a column to retrieve the comments). Please help!



Function Comment(incell) As String
On Error Resume Next
Comment = incell.Comment.Text
End Function

 

[Zubair Muhammad]

Hi Tamara,

Use this code. Modify the myrange according to your needs.

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:Z100")
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("AA1").Offset(i - 1) = myarray(i)
Next i
End Sub

 

[Zubair Muhammad]

Basically above code will scan all the cells in the range("A1:Z100") and record the comments found in a single column AA starting from row 1.

So modify range("A1:Z100") and Range("AA1") according to your situation

 

[tamrob23]

I just ran it and this is perfect! With the way you arranged the range, I can manipulate each workbook as to where I would like for the comments to go. Thank you so much Mahammad, have a great day!