改了一下代码。测试一下
Sub test()
Dim i%, j%, dic As Object, d As Object
Dim dataArr, brr, n
Set dic = CreateObject("scripting.dictionary")
Set d = CreateObject("scripting.dictionary")
dataArr = ActiveSheet.[a2].CurrentRegion
For i = 3 To UBound(dataArr)
For j = 2 To UBound(dataArr, 2) Step 2
If Len(dataArr(i, j)) > 0 Then
d(dataArr(i, j)) = dataArr(i, 1)
If dic.exists(dataArr(i, 1)) Then
dic(dataArr(i, 1)) = dic(dataArr(i, 1)) & " " & dataArr(i, j)
Else
dic(dataArr(i, 1)) = dataArr(i, j)
End If
End If
Next j
Next
Erase dataArr
If dic.exists(Range("B17").Value) Then
dataArr = Split(dic(Range("b17").Value))
ReDim brr(1 To UBound(dataArr) + 1, 1 To 2)
For i = 0 To UBound(dataArr)
n = n + 1
brr(n, 1) = i + 1
brr(n, 2) = dataArr(i)
Next
Else
dataArr = Split(dic(d(Range("b17").Value)))
ReDim brr(1 To UBound(dataArr) + 2, 1 To 2)
n = 1
For i = 0 To UBound(dataArr)
If Val(dataArr(i)) <> Range("b17").Value Then
brr(1, 2) = d(Range("b17").Value)
brr(1, 1) = 1
n = n + 1
brr(n, 1) = n
brr(n, 2) = dataArr(i)
End If
Next
End If
[a20:b100].ClearContents
Range("A20").Resize(UBound(brr), 2) = brr
End Sub