Excel Forum

Code: Select all

Private Sub CommandButton2_Click()
  
    [color=#BF0000]Sheets("Table record").Select
    Range("AJ6").AddComment
    Range("AJ6").Comment.Visible = False
    Range("AJ6").Comment.Text Text:="'ER stroke form'!R[20]c[-45]"
    Sheets("ER stroke form").Select[/color]
End Sub

Code: Select all

Dim rsTemp As Range, arrComment() As Variant, rs As Range
Dim rtAll As Range, rtCol As Range, i As Integer, j As Integer
With Sheets("ER stroke form")
    Set rsTemp = .Range("m26:m35")
    i = 0
    For Each rs In rsTemp
        If rs.Value <> "" Then
            ReDim Preserve arrComment(i)
            arrComment(i) = rs.Value
            i = i + 1
        End If
    Next rs
End With
With Sheets("Table record")
    Set rtAll = .Range("ah6:ba12")
    For i = 1 To rtAll.Rows.Count
        j = 0
        Set rtCol = rtAll.Rows(i)
        For Each r In rtCol.Cells
            If r.EntireColumn.Hidden = False Then
                If r.Comment Is Nothing Then
                    r.AddComment
                    r.Comment.Text Text:=arrComment(j)
                Else
                    r.Comment.Text Text:=arrComment(j)
                End If
                j = j + 1
            End If
        Next r
    Next i
End With

Code: Select all

Dim rsTemp As Range, arrComment() As Variant, rs As Range
Dim rtAll As Range, rtCol As Range, i As Integer, j As Integer
With Sheets("ER stroke form")
    Set rsTemp = .Range("m26,m27,m28,m29,m30,m31,m32,m33,m34,m35")
    i = 0
    For Each rs In rsTemp
        If rs.Value <> "" Then
            ReDim Preserve arrComment(i)
            arrComment(i) = rs.Value
            i = i + 1
        End If
    Next rs
End With
With Sheets("Table record")
    Set rtAll = .Range("ag6:ag101,ah6:ah101,aj6:aj101,am6:am101,ap6:ap101,as6:as101,au6:au101,ax6:ax101,ba6:ba101")
    For i = 1 To rtAll.Rows.Count
        j = 0
        Set rtCol = rtAll.Rows(i)
        For Each r In rtCol.Cells
            If r.EntireColumn.Hidden = False Then
                If r.Comment Is Nothing Then
                    r.AddComment
                    r.Comment.Text Text:=arrComment(j)
                Else
                    r.Comment.Text Text:=arrComment(j)
                End If
                j = j + 1
            End If
        Next r
    Next i
End With

Code: Select all

Public Sub AddComments()
Dim arrCols, r As Integer

r = Columns("a:a").Find([HN]).Row

arrCols = Array("AG", "AH", "AJ", "AM", "AP", "AS", "AU", "AX", "BA")
 For Each arr In arrCols
 n = n + 1
 arr = arr & r
 With Range(arr)
     CommentText = [remark].Resize(1, 1).Offset(n - 1, 0).Value
    .AddComment
    .Comment.Visible = False
    .Comment.Text CommentText
 End With
 Next
End Sub

Code: Select all

Dim rsTemp As Range, arrComment() As Variant
Dim rtAll As Range, rs As Range, i As Integer, j As Integer
With Sheets("ER stroke form")
    Set rsTemp = .Range("m26:m35")
    i = 0
    For Each rs In rsTemp
        If rs.Value <> "" Then
            ReDim Preserve arrComment(i)
            arrComment(i) = rs.Value
            i = i + 1
        End If
    Next rs
End With
With Sheets("Table record")
    Set rtAll = .Range("AG5,AH5,AJ5,AM5,AP5,AS5,AU5,AX5,BA5")
    For i = 0 To .Range("ag5", .Range("ag5").End(xlDown)).Rows.Count - 1
        j = 0
        For Each r In rtAll
            If r.Offset(i, 0).Comment Is Nothing Then
                r.Offset(i, 0).AddComment
                r.Offset(i, 0).Comment.Text Text:=arrComment(j)
            Else
                r.Offset(i, 0).Comment.Text Text:=arrComment(j)
            End If
            j = j + 1
        Next r
    Next i
End With