[字典物件]單一欄位的值與多個陣列比對後並分類至不同欄位

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

1^# 跳轉到 »

發表於 2016-10-21 08:06 | 顯示全部帖子

回復 3# greetingsfromtw
不用字典物件也可以試試看

Option Explicit
Sub Ex()
Dim Rng As Range, R As Range, E As Range, i(1 To 2) As Integer
With Range("J1").CurrentRegion
i(1) = .Columns.Count + 1
Set Rng = .Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeConstants) '比對字串
End With
Range([A2], [A2].End(xlDown)).Offset(, 1).Resize(, i(1)) = ""
For Each R In Range([A2], [A2].End(xlDown)) '原始資料迴圈
i(2) = i(1)
For Each E In Rng
If InStr(UCase(R), UCase(E)) Then
i(2) = E.Column - Range("J1").Column + 1
Exit For
End If
Next
With Cells(65536, 1 + i(2))
If .EntireColumn.Find(R, Lookat:=xlPart) Is Nothing Then .End(xlUp).Offset(1) = R
'資料不重複
End With
Next
End Sub

複製代碼

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

靜思自在 : 不要隨心所欲，要隨心教育自己。