参考下列代码(注:结果填充在了T列):
- Sub kkk()
- Dim d As Object, theStr1$, theStr2$, theItemStr$
- Dim theFinalRow&, i&, arr As Variant, brr As Variant
- '
- Set d = CreateObject("SCripting.Dictionary")
- With Sheet7
- theFinalRow = .Cells(.Rows.Count, 13).End(xlUp).Row
- If theFinalRow < 2 Then GoTo The_Exit
- arr = .Range(.Cells(2, 13), .Cells(theFinalRow, 13))
- brr = .Range(.Cells(2, 19), .Cells(theFinalRow, 19))
- If Not IsArray(arr) Then
- ReDim arr(1 To 1, 1 To 1)
- ReDim brr(1 To 1, 1 To 1)
- arr(1, 1) = .Cells(2, 13)
- brr(1, 1) = .Cells(2, 19)
- End If
- '
- For i = 1 To UBound(arr)
- theStr1 = brr(i, 1) 'S列内容
- theStr2 = arr(i, 1) '关键字
- If theStr1 = "" Then
- If d.exists(theStr2) Then brr(i, 1) = d(theStr2)
- Else
- If d.exists(theStr2) Then
- theItemStr = "、" & d(theStr2) & "、"
- If InStr(1, theItemStr, theStr1) = 0 Then
- d(theStr2) = d(theStr2) & "、" & theStr1
- End If
- Else
- d(theStr2) = theStr1
- End If
- End If
- Next i
- .Cells(2, 20).Resize(UBound(brr)) = brr
- End With
- The_Exit:
- Set d = Nothing
- End Sub
复制代码
|