'为了好理解,我直接用单元格写入
Sub test()
Dim i As Integer, rw As Integer, col As Integer
Dim d As Object
Set d = CreateObject("Scripting.Dictionary") '创建字典,用法可以参考蓝版主的字典用法
'###########################################################################################
'将sheet1数据加入字典中
With Sheet1
For i = 2 To .Range("a65536").End(xlUp).Row 'sheet1表中从第二行开始到最后一行
'用b列单元格合并上e列单元格作为字典的key,item则用chr(10)<换行符>合并所有符合的内容
d(.Cells(i, "b") & "\" & .Cells(i, "e")) = d(.Cells(i, "b") & "\" & .Cells(i, "e")) & Chr(10) & .Cells(i, "f")
Next i
End With
'############################################################################################
For rw = 2 To Range("a2").End(xlDown).Row 'sheet2表格是从第二行开始,到最后一行
For col = 3 To Range("a2").End(xlToRight).Column 'sheet2表格是从第3列开始,到最后一列
If Cells(rw, col) <> 0 Then '用if判断单元格是否是0,不是则加批注进去
'先判断单元格是否有批注,如果单元格没有批注则加批注
If Cells(rw, col).Comment Is Nothing Then Cells(rw, col).AddComment
'将内容加入批注中
Cells(rw, col).Comment.Text Text:=Format(Date, "mm.dd") & d(Cells(rw, 1) & "\" & Cells(1, col))
'将批注隐藏
Cells(rw, col).Comment.Visible = False
End If
Next col
Next rw
End Sub