Thursday, 13 September 2012

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

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

If TPR = 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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts