本帖最后由 maditate 于 2017-4-26 12:56 编辑
Sub test()
Set d = CreateObject("Scripting.Dictionary")
rr = Cells(Rows.Count, 1).End(xlUp).Row
arr = Range("A1:B" & rr)
For h = LBound(arr) + 1 To UBound(arr)
d(arr(h, 1)) = arr(h, 2)
Next h
rr = Cells(Rows.Count, 3).End(xlUp).Row
arr = Range("C1:D" & rr)
For h = LBound(arr) + 1 To UBound(arr)
If d.Exists(arr(h, 1)) = False Then
pp = Empty
l = Len(arr(h, 1))
If l = 2 Then
r1 = Mid(arr(h, 1), 1, 1)
r2 = Mid(arr(h, 1), 2, 1)
a = d.Item(r1)
pp = Mid(a, 1, 2)
a = d.Item(r2)
pp = pp & Mid(a, 1, 2)
d(arr(h, 1)) = pp
ElseIf l = 3 Then
r1 = Mid(arr(h, 1), 1, 1)
r2 = Mid(arr(h, 1), 2, 1)
r3 = Mid(arr(h, 1), 3, 1)
a = d.Item(r1)
pp = Mid(a, 1, 1)
a = d.Item(r2)
pp = pp & Mid(a, 1, 1)
a = d.Item(r3)
pp = pp & Mid(a, 1, 2)
d(arr(h, 1)) = pp
ElseIf l >= 4 Then
r1 = Mid(arr(h, 1), 1, 1)
r2 = Mid(arr(h, 1), 2, 1)
r3 = Mid(arr(h, 1), 3, 1)
r4 = Mid(arr(h, 1), l, 1)
a = d.Item(r1)
pp = Mid(a, 1, 1)
a = d.Item(r2)
pp = pp & Mid(a, 1, 1)
a = d.Item(r3)
pp = pp & Mid(a, 1, 1)
a = d.Item(r4)
pp = pp & Mid(a, 1, 1)
d(arr(h, 1)) = pp
End If
End If
Next h
K = d.Keys
t = d.Items
ThisWorkbook.Sheets("Sheet1").Range("H2").Resize(UBound(t) + 1) = Application.Transpose(t) '此行 H2 可以改为其它单元格,
ThisWorkbook.Sheets("Sheet1").Range("G2").Resize(UBound(K) + 1) = Application.Transpose(K) '此行 G2 可以改为其它单元格,
End Sub
|