Need to put comments on autofilter data

[danpre]

Hi,

I Have a data in excel file where i have autofilter Column D.

Need to put comments in Column E for every row.

Please help me with the VBA code as i have tried a lot but not able go through.

Thanks in advance for your help.

Thanks

 

[Gary McMaster]

Maybe this?

Gary

Public Sub Comment_Add()

Dim oCell As Range
Dim oVisible As Range

Dim sCommentText As String

Set oVisible = ActiveSheet.Range("D2:D" & ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)

For Each oCell In oVisible
    sCommentText = oCell.Text & " : " & Format(Date, "mm/dd/yy")
    oCell.Offset(0, 1).AddComment sCommentText
Next oCell

End Sub


Public Sub Comment_Delete()

Dim oComment As Comment

For Each oComment In ActiveSheet.Comments
    If oComment.Parent.Column = 5 Then oComment.Delete
Next oComment

End Sub

 

[danpre]

Hi Gary,

Thanks for this code.

What i am asking is that when i filter the data in column D, i need to put some text in Column E on Visible cell

For eg

Column D Column E

D2 PO
D10 PO
D12 PO
D15 PO

I need the code for the above example.

 

[Gary McMaster]

If your autofilter (on column "D") is showing rows 2,10,12 & 15 then then code I posted should put a comment in E2, E10, E12 & E15. The comment text should be the text in column "D" concatenated with a colon and today's date.

If you want to change the text to be just "PO" then change this line:

sCommentText = oCell.Text & " : " & Format(Date, "mm/dd/yy")

to:

sCommentText = "PO"

If this is not what you are looking for then I don't know what you're asking for.

Gary

 

[danpre]

Thanks you so much Gary - Code you provided was for inserting a comment in visible cell.

I needed the code for inserting value in cell.

I corrected that and it is working absolutely fine.

Thanks

 

[danpre]

Gary,

Could u tell me what the below Code will do

Public Sub Comment_Delete()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Dim oComment As Comment<o></o>
<o> </o>
For Each oComment In ActiveSheet.Comments<o></o>
If oComment.Parent.Column = 5 Then oComment.Delete<o></o>
Next oComment<o></o>
<o> </o>
End Sub<o></o>


Thanks

 

[Gary McMaster]

It deletes all of the comments in column 5 ("E")

It was meant to undo the effects of the other procedure.

Gary

 

[danpre]

Ok - I got it
Thanks