Extract comments (Not notes) from excel

[IamLost]

Hello, I have looked through the search and cant find anything, also google is not helping. I have inherited an excel spreadsheet which has a lot of data as well as a lot of comments. I am looking for a way to extract the comment into a new cell. Everything I read online refers to the "old" comments which are now called Notes I believe.

To simplify, I have a spreadsheet with for e.g. Column A has information in the cell as well as a comment. What I want to be able to do is run a macro to extract or copy the information in the comment and paste this in the cell to the right.

Any help would be much appreciated.

 

[Zot]

Have a look here. Let us know if you need further assistance

Excel VBA Get Comment from Cell

Hello, I'm having trouble extracting a comment from a cell, here's my code: Dim varComment As String varComment = Cells(A1).Comment.Text MsgBox varComment

www.mrexcel.com

 

[My Aswer Is This]

Try this:
Looks down column A for comments assuming you also have some values in column A

Sub My_Commnets()
'Modified 12/14/2020  8:03 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Not Cells(i, 1).Comment Is Nothing Then Cells(i, 2).Value = Cells(i, 1).Comment.Text
    Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

Sub IamLost()
   Dim Cmt As CommentThreaded
   For Each Cmt In ActiveSheet.CommentsThreaded
      Cmt.Parent.Offset(, 1).Value = Cmt.Text
   Next Cmt
End Sub

 

[IamLost]

Have a look here. Let us know if you need further assistance

Excel VBA Get Comment from Cell

Hello, I'm having trouble extracting a comment from a cell, here's my code: Dim varComment As String varComment = Cells(A1).Comment.Text MsgBox varComment

www.mrexcel.com

Hello, Thank you for that, I had a read through and tried the code but it doesnt work. When i delete the comment and try a note, it does provide a popup with the text. But nothing happens when I have a comment. Not sure if its my terminology I have wrong or not?

 

[Fluff]

Not sure if its my terminology I have wrong or not?

No your terminology is correct, you are talking about the new Threaded comments.

 

[IamLost]

Try this:
Looks down column A for comments assuming you also have some values in column A

Sub My_Commnets()
'Modified 12/14/2020  8:03 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Not Cells(i, 1).Comment Is Nothing Then Cells(i, 2).Value = Cells(i, 1).Comment.Text
    Next
Application.ScreenUpdating = True
End Sub

Hello similar to my previous reply, this code works perfect when its a "note" and not a "Comment. Again apologies as I may be getting the two confused or describing it wrong.

 

[IamLost]

No your terminology is correct, you are talking about the new Threaded comments.

Thank you. For your code, when I try this I get an error message saying 400 or if I enter as a module, I gert Run time error 1004. Sorry my knowledge of VBA is poor at best.

 

[Fluff]

When you put the code in a standard module, what is the error message that you get?

 

[My Aswer Is This]

Hello similar to my previous reply, this code works perfect when its a "note" and not a "Comment. Again apologies as I may be getting the two confused or describing it wrong.

View attachment 27911

You subject Title says:

Try this:
Looks down column A for comments assuming you also have some values in column A

Sub My_Commnets()
'Modified 12/14/2020  8:03 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Not Cells(i, 1).Comment Is Nothing Then Cells(i, 2).Value = Cells(i, 1).Comment.Text
    Next
Application.ScreenUpdating = True
End Sub

You subject Title says: Extract comments (Not notes) from excel
My script looks for comments not notes.