Add/Delete/Change Comments in a Range

[gatarossi]

Dear all,

I have a report to do, with the following fields:


A B C
1 Group Channel Sales
2 He Hypermarket 300
3 He Multishop 700
4 CA Export 150

Until now everything is ok but I need to give support for this data in
this report. So I need to insert comments in the sales range in order
to inform the customers in each channel.

For this I have another support report:

A B
C D E
1 Group Channel Customer
Sales Profitability
2 HE Hypermarket Carrefour
100 10%
3 HE Hypermarket Extra
200 5%
4 HE Multishop Wall Mart
400 6%
5 HE Multishop Ponto
300 12%
6 CA Export Panas
150 30%

So I need to insert a comment in cell C2 this data:

Customer Sales Profitability
Carrefour 100 10%
Extra 200 5%

C3 this data

Customer Sales Profitability
Wall Mart 400 6%
Ponto 300 12%

Thanks in advance!

André

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim LastRow2 As Long
Dim sh As Worksheet
Dim cmt As String

Set sh = Worksheets("Sheet2")
With sh

LastRow2 = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow

cmt = ""
For j = 2 To LastRow2

If LCase(.Cells(i, "A").Value) = LCase(sh.Cells(j,
"A").Value) And _
.Cells(i, "B").Value = sh.Cells(j, "B").Value Then

cmt = cmt & sh.Cells(j, "C").Text & " " & sh.Cells(j,
"D").Text & _
" " & sh.Cells(j, "E").Text & vbNewLine
End If
Next j
If cmt <> "" Then

cmt = "Customer" & " " & "Sales" & " " & "Profitability" &
vbNewLine & cmt
.Cells(i, "C").Comment.Delete
.Cells(i, "C").AddComment cmt
End If
Next i
End With

End Sub

--
__________________________________
HTH

Bob

Dear all,

I have a report to do, with the following fields:


A B C
1 Group Channel Sales
2 He Hypermarket 300
3 He Multishop 700
4 CA Export 150

Until now everything is ok but I need to give support for this data in
this report. So I need to insert comments in the sales range in order
to inform the customers in each channel.

For this I have another support report:

A B
C D E
1 Group Channel Customer
Sales Profitability
2 HE Hypermarket Carrefour
100 10%
3 HE Hypermarket Extra
200 5%
4 HE Multishop Wall Mart
400 6%
5 HE Multishop Ponto
300 12%
6 CA Export Panas
150 30%

So I need to insert a comment in cell C2 this data:

Customer Sales Profitability
Carrefour 100 10%
Extra 200 5%

C3 this data

Customer Sales Profitability
Wall Mart 400 6%
Ponto 300 12%

Thanks in advance!

André