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?

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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