- 帖子
- 192
- 主題
- 15
- 精華
- 0
- 積分
- 194
- 點名
- 0
- 作業系統
- windows
- 軟體版本
- office2010
- 閱讀權限
- 20
- 性別
- 女
- 註冊時間
- 2016-9-22
- 最後登錄
- 2020-8-28
 
|
回復 56# c_c_lai
那ㄟ安捏...- Sub AuditCustPkg(Adt_Rng As Range)
- Dim c As Range, frstAddr As String, tf As Boolean
- Dim cts As Integer, ct2 As Integer
- Dim Arr As Variant, Ar2 As Variant, Ar3 As Variant
-
- With Sheets("Cus簡碼")
- Set c = .[B:B].Find(Adt_Rng.Offset(, -1).Value, , , 1) ' "TR排機&產出" Customer 比對 "Cus簡碼" CUST_GROUP
-
- If Not c Is Nothing Then
- frstAddr = c.Address
- Do
- If IsEmpty(Arr) Then ReDim Arr(1 To 1) Else ReDim Preserve Arr(1 To UBound(Arr) + 1)
- Arr(UBound(Arr)) = Array(c.Offset(, -1).Text, c.Text)
-
- Set c = .[B:B].FindNext(c)
- Loop While Not c Is Nothing And c.Address <> frstAddr
- End If
- End With
-
- If Not IsEmpty(Arr) Then
- With Sheets("材料")
- For cts = LBound(Arr) To UBound(Arr)
- Set c = .[M:M].Find(Arr(cts)(0), , , 1) ' "Cus簡碼" CODE 比對 "材料" CUST_CODE
-
- If Not c Is Nothing Then ' Arr(cts)(0) = "ASM" : Variant/String
- frstAddr = c.Address
- Do
- ' 以 "TR排機&產出" 的 "F"、"G"、"H" 為條件，去 "材料" 找到對應的數據。
- ' 第 1 種 (相同 Cust (c.Value) & PKG (c.Offset(, 3)) & B/S (c.Offset(, 4)) & L/C (c.Offset(, 5)))
- If c.Offset(, 3) = Adt_Rng.Value And c.Offset(, 4) = Adt_Rng.Offset(, 1).Value And c.Offset(, 5) = CStr(Adt_Rng.Offset(, 2).Value) Then
- If IsEmpty(Ar2) Then ReDim Ar2(1 To 1) Else ReDim Preserve Ar2(1 To UBound(Ar2) + 1)
- Ar2(UBound(Ar2)) = Array(c.Text, Arr(cts)(1), c.Offset(, 3).Text, c.Offset(, 4).Text, c.Offset(, 5).Text, c.Offset(, 39).Text, c.Offset(, 40).Text)
- End If
-
- Set c = .[M:M].FindNext(c)
- Loop While Not c Is Nothing And c.Address <> frstAddr
- End If
-
- If Not IsEmpty(Ar2) Then
- For ct2 = LBound(Ar2) To UBound(Ar2)
- ' 以 工作表 "TR排機&產出" 的 "F"、"G"、"H" 為條件， 去 工作表 "材料" 找到對應的數據；
- ' 然後找到這筆數據的 "CARRIER1 P/N"，然後只要一樣 "CARRIER1 P/N" 的都列出來。
- Set c = .[BA:BA].Find(Ar2(ct2)(6), , , 1) ' CARRIER1 P/N ("BA") Ar2(ct2)(6) = "4100998111" :
-
- If Not c Is Nothing Then ' PKG (c.Offset(, -37)) 、 BODU_SIZE (c.Offset(, -36))
- frstAddr = c.Address ' CUST_CODE (c.Offset(, -40).Text)、, LEAD_COUNT (c.Offset(, -35).Text)
- Do
- ' 同步地排除原先在 工作表 "TR排機&產出" 點選的 Package。(Customer、Package、Bodysize)
- ' 修正以 "Cus簡碼" Arr 之第一組 (Arr(1)(0)) 作為判斷依據。
- tf = (c.Offset(, -40).Text = Arr(1)(0) And c.Offset(, -37) = Adt_Rng.Value And c.Offset(, -36) = Adt_Rng.Offset(, 1).Value)
- If Ar2(ct2)(1) <> "" And tf = False Then
- If IsEmpty(Ar3) Then ReDim Ar3(1 To 1) Else ReDim Preserve Ar3(1 To UBound(Ar3) + 1)
- Ar3(UBound(Ar3)) = Array(Ar2(ct2)(1), c.Offset(, -37).Text, c.Offset(, -36).Text, c.Offset(, -35).Text, c.Text)
- End If
- Set c = .[BA:BA].FindNext(c)
- Loop While Not c Is Nothing And c.Address <> frstAddr
- End If
- Next ct2
- End If
- Next cts
- End With
-
- If Not IsEmpty(Ar3) Then CustPkg (Ar3)
- End If
-
- Set Arr = Nothing
- Set Ar2 = Nothing
- Set Ar3 = Nothing
- End Sub
|
|
發表於 2016-11-30 16:23
| 
