Copying and Pasting CommentThreaded

[mayoung]

I have the following code that will copy and paste Notes but also want to copy and paste CommentThreaded. How can I modify this code to do both?
I have tried a few things and can not make it work. Thank You in advance.

Public Sub Macro3()

     Windows("Book1.xlsm").Activate

    Sheets("Sheet1").Activate

    Range("C1").Activate
    Do Until ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Activate
        
        If Len(ActiveCell.NoteText) > 0 Then
            ActiveCell.Copy
            HasNote = True
        End If
    
    ToFind = ActiveCell.Value

    Windows("Book2.xlsm").Activate
    Sheets("Sheet1").Activate
    Columns("C:C").Select
    On Error Resume Next
    Selection.Find(What:=ToFind, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        If Err.Number = 0 Then
            On Error GoTo -1
                If HasNote Then
                ActiveCell.PasteSpecial
                End If
        
        Else
            NotFound = True
            On Error GoTo -1
        End If
         Application.CutCopyMode = False

     Loop
        
End Sub

 

[Micron]

don't you have the answer to that here?

VBA For Filtering Comments in a Column

I am using Office 365 excel Is there away to filter Comments NOT Notes in a Column? Can be using a helper column if necessary. I have the ability to filter Notes in a column but want to filter Comments. Any help would be appreciated.

www.mrexcel.com

 

[mayoung]

don't you have the answer to that here?

VBA For Filtering Comments in a Column

I am using Office 365 excel Is there away to filter Comments NOT Notes in a Column? Can be using a helper column if necessary. I have the ability to filter Notes in a column but want to filter Comments. Any help would be appreciated.

www.mrexcel.com

That’s for filtering. I want to copy and paste keep getting Errors with what I try. I can copy and paste the notes ok but Commentthreaded gives Errors. I actually need to copy and paste both Notes and Commentthreaded both in same column just different Rows have either or.

 

[Micron]

First, NoteText is a method of a range object, not the ActiveCell. So either of these could work
If Not Range("K2").NoteText = "" Then << evaluates to true if cell contains a note with characters
or
If Len(Range("K2").NoteText) = 0 Then
Obviously you will need to replace K2 with either a cell address or set a range object to an address, row/column reference, offset, etc. for each iteration of your loop.
IMO, you really should use Option Explicit as you have undeclared variables. I also don't see the need for HasNote as well as NotFound. If it wasn't found, isn't HasNote just false?

 

[mayoung]

First, NoteText is a method of a range object, not the ActiveCell. So either of these could work
If Not Range("K2").NoteText = "" Then << evaluates to true if cell contains a note with characters
or
If Len(Range("K2").NoteText) = 0 Then
Obviously you will need to replace K2 with either a cell address or set a range object to an address, row/column reference, offset, etc. for each iteration of your loop.
IMO, you really should use Option Explicit as you have undeclared variables. I also don't see the need for HasNote as well as NotFound. If it wasn't found, isn't HasNote just false?

I will be honest I am self taught in VBA and only dabble in this. So not sure how to take what you wrote and incorporate it into the code I have to make it work for coping and pasting over both.

 

[Micron]

No one taught me either. I learn by helping. I 'google' to find what I want and try to adapt and apply. If it doesn't work, I research some more and try again. Then when I solve the issue (and I usually do but might not be the first) I'll probably forget most of what I learned. The grey matter is not what it used to be.
So methinks you could trap an error regarding anything to do with the note, but AFAIK, this is about the new "comment" formerly known as "note" but not about "threadedcomment". So I'd try detecting if there is a comment since a comment can be empty. You can prove that by creating one in a cell then deleting all of its text but not the the note/comment. It remains.
If I declare a range variable: Dim rng As Range
and change your code to

Do Until ActiveCell.Value = ""
     ActiveCell.Offset(1, 0).Activate
     Set rng = ActiveCell
     If Not rng.Comment Is Nothing Then
          ActiveCell.Copy
          HasNote = True
     End If

I can get past that error. However, if there is no note your loop continues to execute and will try to do things that it probably should not. Modify as shown then step through the code and see if a) you get past the error and b) it does things that it should not when there is no note. In that case, you put all that you want to do when there is a note inside the IF block that tests for a note.

 

[mayoung]

No one taught me either. I learn by helping. I 'google' to find what I want and try to adapt and apply. If it doesn't work, I research some more and try again. Then when I solve the issue (and I usually do but might not be the first) I'll probably forget most of what I learned. The grey matter is not what it used to be.
So methinks you could trap an error regarding anything to do with the note, but AFAIK, this is about the new "comment" formerly known as "note" but not about "threadedcomment". So I'd try detecting if there is a comment since a comment can be empty. You can prove that by creating one in a cell then deleting all of its text but not the the note/comment. It remains.
If I declare a range variable: Dim rng As Range
and change your code to

Do Until ActiveCell.Value = ""
     ActiveCell.Offset(1, 0).Activate
     Set rng = ActiveCell
     If Not rng.Comment Is Nothing Then
          ActiveCell.Copy
          HasNote = True
     End If

I can get past that error. However, if there is no note your loop continues to execute and will try to do things that it probably should not. Modify as shown then step through the code and see if a) you get past the error and b) it does things that it should not when there is no note. In that case, you put all that you want to do when there is a note inside the IF block that tests for a note.

Thank You for the guidance in the right direction. The code you provided above only did the Notes but not the CommentThreaded.
So I was able to add to your code to make it work.

    Do Until ActiveCell.Value = ""
     Windows(LatestFile).Activate
     ActiveCell.Offset(1, 0).Activate
     Set Rng = ActiveCell
     If Not Rng.Comment Is Nothing Then
          ActiveCell.Copy
          HasNote = True
          
  Else

     Set Rng = ActiveCell
     If Not Rng.CommentThreaded Is Nothing Then
          ActiveCell.Copy
          HasNote = True
    
     End If
     End If

 

[Micron]

Ahh, yes; that is what I meant by didn't you have the answer (how to deal with commentsThreaded) in the other thread.
But then I thought your stumbling block was this:

I want to copy and paste keep getting Errors with what I try.

 

[mayoung]

Ahh, yes; that is what I meant by didn't you have the answer (how to deal with commentsThreaded) in the other thread.
But then I thought your stumbling block was this:

I was getting errors so with your guidance have worked around the errors.