How to Add Cell Contents in Comments Box with Excel VBA Macro

Excel VBA Macro To Add Cell Contents In Comment Box

The Following Macro is Designed to Perform a Requirement in Reporting.From the Following Macro you can pick a Piece of code you required.

Sub AddComments()

Dim X As Integer

Dim y As Integer

Dim z As Integer

Dim a As Integer

Dim TPR As String

Msg= MsgBox("Press OK to Conitnue else Press CANCEL to Exit", vbOKCancel, "Macro To Add Comments")

If Msg= vbCancel Then GoTo Handler:

For X = 2 To 1000 'Loop to Update Existing Data

For y = 7 To 10

Cells(X, y).ClearContents

Cells(X, y).ClearComments

Next y

Next X

z = 1

For X = 2 To 1000

If Cells(X, 5) = "Y" Then

z = z + 1 'Counting Variable

For y = 2 To 1000

If Cells(y, 1) <> "" And Cells(y, 1) = Cells(X, 1) And Cells(y, 5) = "Y" Then

Cells(z, 7) = Cells(y, 1)

Cells(z, 8) = Cells(z, 8) + Cells(y, 4) 'Sum here

Cells(z, 10) = Cells(z, 10) & Cells(y, 2) & " , " & Cells(y, 3) & "  -  "

End If

Next y

End If

Next X

'Loop to Add Cell Contents to Comment Box

For a = 2 To 1000  

Cells(a, 9).AddComment ("CompanyId,VersionId:" & Cells(a, 9) & Cells(a, 10))

Next a

For X = 1000 To 1 Step -1

For y = 1000 To 1 Step -1

If y <> X Then

If Cells(y, 7) <> "" And Cells(y, 7) = Cells(X, 7) Then

a = a + 1

If a > 2 Then  'Duplicates Count here

Cells(y, 7).Delete shift:=xlUp

Cells(y, 8).Delete shift:=xlUp

Cells(y, 9).Delete shift:=xlUp

Cells(y, 10).Delete shift:=xlUp

Application.ScreenUpdating = False  'Stops Screen Updation.

End If

End If

End If

Next y

Next X

MsgBox "Macro Process Completed"

Handler:

End Sub

Thanks,Tamatam