Adding comments to cells

I have a sheet that contains multiple tables. In the header of each table in column D are the letters Sa. I search until I find those letters. I then store that row number in StRow. I then loop through until I find a blank cell in column D. This row number is stored in rowloop. I put the value of cell(rowloop, "B") into Comtxt. What I want to do is add the text in Comtxt to each cell in column D from Strow to rowloop-1. Right now I am getting a Compiler error at the .Add Comment line of invalid or unqualified reference. This is where my object oriented programming is lacking. What is the piece I need to add the comment?

Private Sub Comment()

  Dim LastRow As Long
  Dim StartRow As Long
  Dim StRow As Long
  Dim rowloop As Long
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("test")
    StartRow = 3
    LastRow = Wks.Cells(Rows.Count, "D").End(xlUp).Row
        For Each Comp In Wks.Range(Cells(StartRow, "D"), Cells(LastRow, "D"))
                Select Case Comp
                 Case Is = "Sa"
                     StRow = Row.Count + 1
                     rowloop = StRow
                       Do While Cells(rowloop, "D").Value <> ""
                          rowloop = rowloop + 1
                       Loop
                     Comtxt = Cells(rowloop, "B").Value
                     For Each Res In Wks.Range(Cells(StRow, "D"), Cells(rowloop - 1 < "D"))
                        .AddComment
                        .Comment.Text Text:=Comtxt
                    Next Res
                End Select
            Next Comp
End Sub

Hello Bope,

The period prefix indicates the property or method is belongs to a With ... With End statement. You could use With in the loop like this...

                     For Each Res In Wks.Range(Cells(StRow, "D"), Cells(rowloop - 1 < "D"))
                       With Res
                          .AddComment
                          .Comment.Text Text:=Comtxt
                       End With
                    Next Res

Another problem you can encounter is trying to Add a comment to cell that already has a comment. You should check before doing so. If the comment exists then change the Comment's text to the new text. I modified your code to this.

Private Sub Comment()

  Dim Comm As Comment
  Dim LastRow As Long
  Dim StartRow As Long
  Dim StRow As Long
  Dim rowloop As Long
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("test")
    StartRow = 3
    LastRow = Wks.Cells(Rows.Count, "D").End(xlUp).Row
        For Each Comp In Wks.Range(Cells(StartRow, "D"), Cells(LastRow, "D"))
                Select Case Comp
                 Case Is = "Sa"
                     StRow = Row.Count + 1
                     rowloop = StRow
                       Do While Cells(rowloop, "D").Value <> ""
                          rowloop = rowloop + 1
                       Loop
                     Comtxt = Cells(rowloop, "B").Value
                     For Each Res In Wks.Range(Cells(StRow, "D"), Cells(rowloop - 1 < "D"))
                        Set Comm = Res.Comment
                          If Not Comm Is Nothing Then
                            Comm.Text = ComTxt
                          Else
                            Res.AddComment
                            Res.Comment.Text = Comtxt
                          End If
                    Next Res
                End Select
            Next Comp
End Sub

Sincerely,
Leith Ross

Leith,
Thanks for the help and the VBA lesson. I did have to make a couple other tweaks. I have highlighted those in Red. The one thing I struggled with was the If statement to determine if a comment existed. What I finally had to do was delete the old Comment and add a new one. If you have a more elegant suggestion I would love to see it.

Thanks,
Eric

Private Sub Comment()

  Dim Comm As Comment
  Dim LastRow As Long
  Dim StartRow As Long
  Dim StRow As Long
  Dim rowloop As Long
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("test")
    StartRow = 3
    LastRow = Wks.Cells(Rows.Count, "D").End(xlUp).Row
        For Each Comp In Wks.Range(Cells(StartRow, "D"), Cells(LastRow, "D"))
                Select Case Comp
                 Case Is = "Sa"
                     StRow = Comp.Row + 1
                     rowloop = StRow
                       Do While Cells(rowloop, "D").Value <> ""
                          rowloop = rowloop + 1
                       Loop
                     Comtxt = Cells(rowloop, "B").Value
                     For Each Res In Wks.Range(Cells(StRow, "D"), Cells(rowloop - 1, "D"))
                        Set Comm = Res.Comment
                          If Not Comm Is Nothing Then
                            Res.Comment.Delete
                            Res.AddComment
                            Res.Comment.Text Text:=Comtxt
                          Else
                            Res.AddComment
                            Res.Comment.Text Text:=Comtxt
                          End If
                    Next Res
                End Select
            Next Comp
End Sub

Hello Bope,

Your solution is easy and direct. You could also clear the old comment's text by assigning it an empty string and then assign the new text. Either way is fine.

Sincerely,
Leith Ross