Problem combining comments from different cells to another cell with vba

**lairscah** · 06-12-2014, 01:14 AM

Hi, I have learned a lot from the great advice from this forum. I am fairly comfortable with visual basic but This one has me stumped. I need a macro to combine one cell's comments with another cell's. My spreadsheet accumulates data and time stamped, progressive comments are necessary. I have no trouble combining two cell's comments if both cells have comments(i.e activecell.comments.text text:=range("A1").comment.text & Chr(10) & range("B1").comment.text etc..). The wrinkle I am running into is if one of the cells has no comment my macro "bugs" out. If this could be done by excel formula I would not have to post this thread. Instead of giving you an example of my macro here is basically what I need.

I need to combine the comments from A1 and B1 into C1. If A1 has no comment I need the code to bypass that and just add the comment from B1(and vice versa if A1 has a comment and B1 does not). I have tried if conditions and else commands but I must be mucking it up somewhere. I have spent a lot of time searching online and through these forums to help myself and not bother you fine people. Any help in the right direction would be most appreciated.

**AlphaFrog** · 06-12-2014, 02:15 AM

Try something like this...

Sub Combine_Comments()
    Dim cell As Range, cmt As Comment
    For Each cell In Range("A1:C1")
        If HasComment(cell) Then
            If cmt Is Nothing Then
                Set cmt = cell.Comment
            Else
                cmt.Text Text:=cmt.Text & vbLf & cell.Comment.Text
            End If
        End If
    Next cell
End Sub
    
Function HasComment(ByRef rng As Excel.Range) As Boolean
    Dim cmt As Excel.Comment
    On Error Resume Next
    Set cmt = rng.Comment
    HasComment = Not cmt Is Nothing
End Function