Snasui Excel Forum

Code: Select all

Option Explicit
Sub AddCommentTest()
    Dim rs As Range
    Dim rt As Range
    Dim r As Range
    Dim i As Integer
    With Sheets("ISO9001Clause")
        Set rs = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    End With
    Set rt = Sheets("cOMMENTS").Range("B2")
    For Each r In rs
        With Sheets("cOMMENTS")
            If Not rt.Offset(i, 0).Comment Is Nothing Then
                rt.Offset(i,0).Comment.Delete
                rt.Offset(i,0).AddComment Text:=r.Value
            Else
                rt.Offset(i,0).AddComment Text:=r.Value
            End If
        End With
        i = i + 1
    Next r
End Sub

Code: Select all

Option Explicit
Sub AddCommentTest()
    Dim rs As Range
    Dim rt As Range
    Dim r As Range
    Dim i As Integer
    With Sheets("ISO9001Clause")
        Set rs = .Range("B2", .Range("B" & Columns.Count).End(xlUp))
    End With
    Set rt = Sheets("cOMMENTS").Range("B2")
    For Each r In rs
        With Sheets("cOMMENTS")
            If Not rt.Offset(0, i).Comment Is Nothing Then
                rt.Offset(0, i).Comment.Delete
                rt.Offset(0, i).AddComment Text:=r.Value
            Else
                rt.Offset(0, i).AddComment Text:=r.Value
            End If
        End With
        i = i + 1
    Next r
End Sub

Code: Select all

Option Explicit
Sub AddCommentTest()
    Dim rs As Range
    Dim rt As Range
    Dim r As Range
    Dim i As Integer
    With Sheets("ISO9001Clause")
        Set rs = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    End With
    Set rt = Sheets("cOMMENTS").Range("B2")
    For Each r In rs
        With Sheets("cOMMENTS")
            If Not rt.Offset(0, i).Comment Is Nothing Then
                rt.Offset(0, i).Comment.Delete
                rt.Offset(0, i).AddComment Text:=r.Value
            Else
                rt.Offset(0, i).AddComment Text:=r.Value
            End If
        End With
        i = i + 1
    Next r
End Sub

Code: Select all

Option Explicit
Sub AddCommentTest()
    With ActiveSheet
        If .Range("A1").comment Is Nothing Then
            'do nothing
        Else
            'delete existing comment
            .Range("A1").comment.Delete
        End If
        .Range("a1").AddComment Text:=.Range("H1").Text
    End With
nd SubE