Thursday, September 13, 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

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog