|
zhj1978 发表于 2014-4-4 13:06
我只是举了个例子,说明情况,其实数据源很多的,请帮忙
完善了下,请再测试哈!{:1712:}- Sub test()
- Dim d As Object
- Dim ar
- Dim i As Long, j As Long
- Dim sr As String
- Application.ScreenUpdating = False
- Cells.ClearComments
- Set d = CreateObject("scripting.dictionary")
- ar = Sheet1.Cells(1, 1).CurrentRegion
- For i = 2 To UBound(ar)
- sr = ar(i, 4) & vbTab & ar(i, 5)
- If d.exists(sr) Then
- d(sr) = d(sr) & vbCrLf & ar(i, 6) & " " & ar(i, 7)
- Else
- d.Add sr, ar(i, 6) & " " & ar(i, 7)
- End If
- Next i
- ar = Sheet2.Cells(1, 1).CurrentRegion
- For i = 3 To UBound(ar)
- For j = 3 To UBound(ar, 2)
- sr = ar(2, j) & vbTab & ar(i, 2)
- If d.exists(sr) Then
- With Cells(i, j)
- .AddComment d(sr)
- .Comment.Shape.TextFrame.AutoSize = True
- End With
- End If
- Next j
- Next i
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|