- 帖子
- 1478
- 主題
- 40
- 精華
- 0
- 積分
- 1502
- 點名
- 0
- 作業系統
- Windows 7
- 軟體版本
- Excel 2010 & 2016
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2020-7-15
- 最後登錄
- 2025-10-23
|
回復 34# 198188
以下請先試試看
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr(1 To 10000, 1 To 18), A, V, Z, Q, S, i&, j%, R&, C%, y%, K, X%, T$, T1$, T2$, T3$, T4$, T5$, Rk$, W, L
Application.ScreenUpdating = False
Set Z = CreateObject("Scripting.Dictionary")
Brr = [Rule!A1].CurrentRegion
For j = 2 To 8
T = Trim(Mid(Brr(2, j), 1, Len(Brr(2, j)) * 2 - LenB(StrConv(Brr(2, j), vbFromUnicode)) - 2))
For i = 3 To UBound(Brr)
If Brr(i, j) <> "" Then Z(Brr(i, j) & "|") = T: Exit For
Next
Next
For i = 3 To UBound(Brr)
For j = 11 To 28
If Brr(i, j) = "" Then
If j = 12 Then
Z("/" & Brr(i, 9) & "/") = Brr(i, 11)
End If
Exit For
End If
Z("/" & Brr(i, j) & "/") = Brr(i, 9)
Z("/" & Brr(i, j) & "//") = Brr(i, 10)
Next
Next
For i = 3 To UBound(Brr)
T = Brr(i, 9)
Rk = ""
For j = 2 To 8
Rk = Rk & Brr(i, j)
Next
S = Brr(i, 1)
Z(T & "\" & Rk) = S
Z(S & "/UR") = Sheets(S).[A65536].End(3)(2).Row
Z(S & "/UC") = Sheets(S).Cells(Z(S & "/UR") - 1, 256).End(xlToLeft).Column
Next
Arr = Sheets("Material").UsedRange
For i = 2 To UBound(Arr)
Z(Arr(i, 3) & "/m") = i
Next
Brr = Range(Sheets("Part List").[M1], Sheets("Part List").[A65536].End(3))
For i = 2 To UBound(Brr)
T5 = Left(Brr(i, 1), 2)
T = Z("/" & T5 & "/")
T4 = Z("/" & Left(Brr(i, 1), 2) & "//")
Rk = Brr(i, 7)
T2 = Z(T & "\" & Rk)
T3 = T & "-" & Z(Rk & "|")
If Len(T3) > 31 Then
T3 = T4 & "-" & Z(Rk & "|")
If Len(T3) > 31 Then
T3 = T5 & "-" & Rk
End If
End If
Z("|" & T3) = T2
A = Z("/" & T3)
R = Z("r/" & T3)
If Not IsArray(A) Then
A = Crr
End If
If T2 = "Bom" Then
R = R + 1
A(R, 1) = R
A(R, 2) = Brr(i, 1)
A(R, 3) = Brr(i, 12)
If A(R, 3) <> "" Then
A(R, 4) = Arr(Z(A(R, 3) & "/m"), 5)
A(R, 5) = Arr(Z(A(R, 3) & "/m"), 6)
A(R, 6) = Arr(Z(A(R, 3) & "/m"), 7)
A(R, 8) = Arr(Z(A(R, 3) & "/m"), 11)
A(R, 9) = Arr(Z(A(R, 3) & "/m"), 10)
A(R, 13) = Arr(Z(A(R, 3) & "/m"), 8)
End If
A(R, 7) = Brr(i, 4) & " x " & Brr(i, 5)
A(R, 10) = Brr(i, 3)
A(R, 18) = Brr(i, 7)
GoTo i01
End If
If T2 = "Gasket" Then
R = R + 1
A(R, 1) = R
A(R, 2) = Brr(i, 1)
A(R, 9) = Brr(i, 3)
A(R, 6) = Brr(i, 5)
A(R, 7) = Brr(i, 6)
A(R, 11) = Brr(i, 7)
A(R, 3) = Brr(i, 12)
If A(R, 3) <> "" Then
A(R, 4) = Arr(Z(A(R, 3) & "/m"), 5)
A(R, 5) = Arr(Z(A(R, 3) & "/m"), 6)
A(R, 8) = Arr(Z(A(R, 3) & "/m"), 8)
End If
GoTo i01
End If
If T2 = "Structural" Then
R = R + 1
A(R, 1) = R
A(R, 2) = Brr(i, 1)
A(R, 3) = Brr(i, 2)
A(R, 5) = Brr(i, 3)
L = Split(Brr(i, 2) & "mm", "mm")(0)
W = Split(Brr(i, 2) & "mm", "mm")(1)
L = Val(StrReverse(Mid(Val(StrReverse(L & 1)), 2)))
W = Val(StrReverse(Mid(Val(StrReverse(W & 1)), 2)))
A(R, 6) = L * W
A(R, 8) = A(R, 5) * A(R, 6) / 1000
A(R, 7) = Application.RoundUp(A(R, 5) * A(R, 6) / 1000, 0)
GoTo i01
End If
If T2 = "DN Material" Then '
R = R + 1
A(R, 1) = R
A(R, 2) = Brr(i, 1)
A(R, 3) = Brr(i, 12)
A(R, 4) = Brr(i, 5)
A(R, 7) = Brr(i, 3)
If A(R, 3) <> "" Then
A(R, 5) = Arr(Z(A(R, 3) & "/m"), 11)
A(R, 6) = Arr(Z(A(R, 3) & "/m"), 8)
A(R, 6) = A(R, 5) * A(R, 7)
End If
A(R, 9) = Brr(i, 7)
GoTo i01
End If
If T2 = "Fabrication Extrusion" Then
R = R + 1
A(R, 1) = R
A(R, 2) = Brr(i, 1)
A(R, 3) = Brr(i, 11)
A(R, 4) = Brr(i, 10)
A(R, 5) = Brr(i, 4)
A(R, 6) = Brr(i, 3)
A(R, 7) = Brr(i, 1)
If A(R, 7) Like "*-*-*" Then
Q = Split(A(R, 7), "-")
A(R, 7) = Q(0) & "-" & Q(1)
End If
A(R, 8) = Brr(i, 7)
End If
i01: Z("/" & T3) = A
Z("r/" & T3) = R
Next
Brr = Range(Sheets("Frame per Dwg").[M1], Sheets("Frame per Dwg").[A65536].End(3))
For i = 2 To UBound(Brr)
T = Z("/" & Left(Brr(i, 2), 2) & "/")
T4 = Z("/" & T & "/")
Rk = Brr(i, 6)
T2 = Z(T & "\" & Rk)
T3 = T4 & "-" & Z(Rk & "|")
Z("|" & T3) = T2
A = Z("/" & T3)
R = Z("r/" & T3)
If Not IsArray(A) Then
A = Crr
End If
If T2 = "Finish" Then
R = R + 1
A(R, 1) = R
A(R, 2) = Brr(i, 2)
A(R, 3) = Brr(i, 3)
A(R, 4) = Brr(i, 4)
A(R, 5) = Brr(i, 5)
A(R, 7) = Brr(i, 6)
End If
Z("/" & T3) = A
Z("r/" & T3) = R
Next
Workbooks.Add
Q = ActiveWorkbook.Name
For Each K In Z.Keys
If IsArray(Z(K)) And Z("r" & K) > 0 Then
T = Mid(K, 2)
ThisWorkbook.Sheets(Z("|" & T)).Copy Before:=Workbooks(Q).Sheets(1)
ActiveSheet.Name = T
With Cells(Z(Z("|" & T) & "/UR"), 1).Resize(Z("r" & K), Z(Z("|" & T) & "/UC"))
.Value = Z(K)
.Borders.LineStyle = xlContinuous
End With
End If
Next
End Sub |
|
發表於 