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.

 

[IamLost]

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

I get this error message and in debugger the second screenshot

 

[Zot]

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?

Guess I'm outdated and old ?

What was previously called Comment is now called Note. It is an annotation. Thanks for question anyway. I get updated now

 

[Fluff]

@My Aswer Is This
On newer versions of Excel the comments you are talking about are now called notes.

 

[My Aswer Is This]

@My Aswer Is This
On newer versions of Excel the comments you are talking about are now called notes.

Yes I know that. He asked about comments not notes. I assume he is using a older version of Excel.

 

[IamLost]

You subject Title says:

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

My understanding was Comments are the new style where you can reply etc and Notes are the older style yellow background. Apologies if I have this the wrong way around.

i am using a fairly new version of excel

 

[Fluff]

@IamLost
I have no idea why you get that error & cannot replicate it.
If there were no comments on the active sheet, then nothing should happen.

 

[Fluff]

He asked about comments not notes.

Exactly, the OP is talking about the new style comments, not the old style ones.

 

[IamLost]

@IamLost
I have no idea why you get that error & cannot replicate it.
If there were no comments on the active sheet, then nothing should happen.

Hmm I will try closing down excel and opening / saving a brand new version of excel. Thank you again for trying it is genuinely appreciated. When you try the code does it work on your version? Just so I can make it as a solution or not.

 

[IamLost]

Guess I'm outdated and old ?

What was previously called Comment is now called Note. It is an annotation. Thanks for question anyway. I get updated now

Every day is a school day. haha. Thank you again for trying

 

[My Aswer Is This]

Every day is a school day. haha. Thank you again for trying

You gave a thumbs up on my answer so did my answer work?