|
不是很清楚你的需求,將取6字元改為 LC 變數,輸入99取全部
items 原來是 Key & 檢查序號,改為 檢查序號 & Key
Sub test4AmoKat()
Dim d, i, j, Arr, K$, x, LC
Set d = CreateObject("Scripting.Dictionary") '字典
Arr = [a1].CurrentRegion 'Arr = A1:D1963
c = UBound(Arr, 2)
LC = 6 '控制為Key的長度,例如6為取前6字為KEY,99為取全部自原為KEY
For i = 1 To c
For j = 2 To UBound(Arr)
If Len(Arr(j, i)) > 6 Then
K = Left(Arr(j, i), LC) '前LC個字元為Key
N = Left(d(K) & " ", 9): Mid(N, i, 1) = i
d(K) = N & K '前面標註123為ABC欄都有
End If
Next
Next
'debug use
Cells(1, c + 3).Resize(65536, 1).ClearContents
Cells(1, c + 3).Resize(d.Count, 1) = Application.Transpose(d.items)
'刪除不完整資料
For Each x In d.items
If Left(x, c) <> Left("123456789", c) Then d.Remove Mid(x, 10, 99)
Next
Cells(1, c + 2).Resize(65536, 1).ClearContents
If d.Count > 0 Then _
Cells(1, c + 2).Resize(d.Count, 1) = Application.Transpose(d.keys)
Set d = Nothing
End Sub |
|