Dim dic As Object
'主程序
Sub test1()
Dim A, B, i%, j%, k%, s%, AreaName$, AreaStart%
A = Sheets(1).UsedRange
Set dic = CreateObject("scripting.dictionary")
For i = 3 To UBound(A)
For j = 4 To UBound(A, 2)
'更新区名(AreaName)和区名所在列(AreaStart)
If Len(A(1, j)) = 2 Then AreaName = A(1, j): AreaStart = j
'如果B列有数据,并不是区名所在列
If A(i, 2) <> "" And Len(A(1, j)) > 2 Then
dic.RemoveAll: s = 0
Call test2(A(i, 2))
Call test2(A(1, j))
B = dic.items
'统计大于1次的数字的数量(s)
For k = 0 To UBound(B)
If B(k) > 1 Then s = s + 1
Next k
'如果数量>1,就显示编号(区名+列),否则显示为空
If s > 1 Then A(i, j) = Left(AreaName, 1) & j - AreaStart Else A(i, j) = ""
End If
Next j
Next i
Sheets(1).Range("a1").Resize(i - 1, j - 1) = A
End Sub
'重复的操作:统计每个数字出现的次数
Sub test2(x)
Dim B, i
B = VBA.Split(x, ",")
For i = 0 To UBound(B)
dic(B(i)) = dic(B(i)) + 1
Next i
End Sub
(, 下载次数: 6)