Replace text in cell with text from comment

[kalagas]

Hi to all.
I was wondering if there is a vba to search inside entire sheet and to do the following:
In cells that there are comments, then replace the text inside these cells with the text of the comments of these cells.
The cells that don't have comments to be leaved as they are.

So i am looking for a batch process to do the above but in sheets i needed not in all workbook.
F.e if a file has sheets 2020, 2021, 2022, 2023 to be able to choose in which sheets of these will run the vba.

Thanks in advance for your time,
Yiannis St

 

[Fluff]

Are you referring to the old style comment (now called notes), or the new style Comments?

 

[Fluff]

Cross posted Replace text in cell with text from comment

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[kalagas]

Are you referring to the old style comment (now called notes), or the new style Comments?

I am using OFFICE 365 latest version

 

[Fluff]

That is not what I asked. Could you please answer my question?
Also please acknowledge post#3

 

[kalagas]

That is not what I asked. Could you please answer my question?
Also please acknowledge post#3

The cross-post is here Replace text in cell with text from comment.
I will upload a excel file cause i don't know the old the new style you mention.
Comments are in F2 and F5

2020 GOOGLE2.xlsx

Microsoft Excel Workbook

1drv.ms

 

[kalagas]

Ok this is solved. The following code from cross-post worked for me.

Sub ShowCommentsOwnCell()

  Application.ScreenUpdating = False

  Dim commrange As Range
  Dim mycell As Range
  Dim WS As Worksheet
  
  Set WS = ActiveSheet

  On Error Resume Next
  Set commrange = WS.Cells.SpecialCells(xlCellTypeComments)
  On Error GoTo 0

  If commrange Is Nothing Then
     MsgBox "no comments found"
     Exit Sub
  End If

  For Each mycell In commrange
      mycell.Value = mycell.Comment.Text
  Next mycell

  Application.ScreenUpdating = True

End Sub

 

[Fluff]

Glad you got it sorted & thanks for letting us know.