- Sub test()
- Dim i, j, d, k, m, arr, brr, s, str
- Set d = CreateObject("Scripting.Dictionary")
- With Sheets("记录")
- arr = .Range("B4:J" & .Cells(.Rows.Count, "j").End(xlUp).Row)
- For i = 1 To UBound(arr)
- If Not d.Exists(arr(i, 8) & "|" & arr(i, 1)) Then
- d(arr(i, 8) & "|" & arr(i, 1)) = arr(i, 9)
- Else
- d(arr(i, 8) & "|" & arr(i, 1)) = d(arr(i, 8) & "|" & arr(i, 1)) & "|" & arr(i, 9)
- End If
- Next
- End With
- With Sheets("填入")
- brr = .Range("A4:U" & .Cells(.Rows.Count, "u").End(xlUp).Row + 1)
- For i = 1 To UBound(brr)
- For j = 10 To UBound(brr, 2) - 1
- If d.Exists(brr(1, j) & "|" & brr(i, UBound(brr, 2))) Then
- s = Split(d(brr(1, j) & "|" & brr(i, UBound(brr, 2))), "|")
- For k = 0 To UBound(s)
- If UBound(s) = 0 Then
- str = s(k)
- Else
- If m = UBound(s) Then
- str = str & s(k) & "。"
- Else
- str = str & s(k) & ";" & vbCrLf
- End If
- m = m + 1
- End If
- Next
- If Not .Cells(i + 4, j).Comment Is Nothing Then .Cells(i + 4, j).Comment.Delete '删除批注
- .Cells(i + 4, j).AddComment "备注说明:" & vbCrLf & str '添加批注
- End If
- Next
- Next
- End With
- End Sub
复制代码
|