Andy2483

回復 9# 准提部林
前輩午安
以下是用三個字典方式處裡,請前輩再指導!
謝謝前輩

Option Explicit
Sub TEST_20221028()
Dim Arr, i&, j&, T1, T2, T3, W, X, Y, Z, C, R
Arr = Range([資料!c1], [資料!a1].Cells(Rows.Count, 1).End(xlUp))
Set X = CreateObject("Scripting.Dictionary")
Set Y = CreateObject("Scripting.Dictionary")
Set W = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
   T1 = Arr(i, 1)
   T2 = Arr(i, 2)
   T3 = Format(Arr(i, 3), "0000")
   Y(T1) = ""
   X(T2 & "|" & T3) = ""
   W(T1 & "|" & T2 & "|" & T3) = T3
Next
ReDim Arr(1 To Y.Count + 1, 1 To X.Count + 1)
i = 1
For Each R In Y.KEYS
   i = i + 1
   Arr(i, 1) = R
   j = 1
   For Each C In X.KEYS
      j = j + 1
      Arr(i, j) = W(R & "|" & C)
      Arr(1, j) = IIf(i = 2, C, Arr(1, j))
   Next
Next
Arr(1, 1) = "　　　原料　編號"
With [呈現表!A1].Resize(UBound(Arr), UBound(Arr, 2))
    .Value = Arr
    .Columns(2).Resize(, UBound(Arr, 2)).Sort Key1:=.Cells(1, 2), Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
    .Rows(2).Resize(UBound(Arr)).Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlNo, Orientation:=xlTopToBottom
    .Rows(1).Replace "|*", "", Lookat:=xlPart
    .Borders.LineStyle = 1
End With
End Sub