換個方向處理, 看是否快一些:
Sub TEST_2()
Dim Arr, Brr, xD, i&, T$, A, B
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([sheet1!A2], [sheet1!A1].Cells(Rows.Count, 1).End(xlUp))
For i = 1 To UBound(Arr)
T = UCase(Arr(i, 1)): Arr(i, 1) = ""
If T <> "" Then xD(T) = Trim(xD(T) & " " & i)
Next i
Brr = Range([sheet2!C1], [sheet2!A1].Cells(Rows.Count, 1).End(xlUp))
For i = 2 To UBound(Brr)
For Each A In Split(UCase(Brr(i, 1) & ";" & Brr(i, 2)), ";")
If A <> "" And xD.Exists(A) Then
For Each B In Split(xD(A), " ")
Arr(B, 1) = Brr(i, 3)
Next
End If
Next
Next i
[sheet1!B2].Resize(UBound(Arr)) = Arr
End Sub
|