|
Sub 循环船号填表() ' By longwin 20240625
Set d = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
arr = Range("A2:C" & [c2].End(xlDown).Row)
For i = 1 To UBound(arr)
If Not d.exists(CStr(arr(i, 1))) Then Set d(CStr(arr(i, 1))) = CreateObject("scripting.dictionary")
If Not d(CStr(arr(i, 1))).exists(arr(i, 3)) Then Set d(CStr(arr(i, 1)))(arr(i, 3)) = CreateObject("scripting.dictionary")
If Not d(CStr(arr(i, 1)))(arr(i, 3)).exists(arr(i, 2)) Then Set d(CStr(arr(i, 1)))(arr(i, 3))(arr(i, 2)) = CreateObject("scripting.dictionary")
d(CStr(arr(i, 1)))(arr(i, 3))(arr(i, 2)) = 1
Next
brr = Range("H7:AM7")
For i = 2 To UBound(brr, 2)
d2(brr(1, i)) = i
Next
ReDim crr(1 To 1000, 1 To 32)
m = 1: j = 0
For Each key1 In d.keys
k = 0: m = m + k
For Each key2 In d(key1).keys
j = 0
For Each key3 In d(key1)(key2).keys
crr(m + j, 1) = key1
crr(m + j, d2(key2)) = key3
j = j + 1
If k <= j Then k = j
Next
Next
Next
Range("H8:AM" & [h8].End(xlDown).Row).ClearContents
[h8].Resize(m + j, 32) = crr
Set d = Nothing
Set d2 = Nothing
End Sub
|
|