- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
22#
發表於 2014-1-25 12:10
| 只看該作者
回復 21# li_hsien
試試看
9#所說: 最後結果是"產品"跟"物料"的項目數會是一樣的沒有錯- Option Explicit
- Sub Ex()
- Dim d As New Collection, AR(), i As Integer, Rng As Range ', e As Variant
- On Error Resume Next 'Collection新增的KEY如被使用或有錯誤
- With Worksheets("產品管控清單")
- For i = 2 To .Range("J1").End(xlDown).Row
- AR = Application.Transpose(Application.Transpose(.Range("A" & i).Resize(, 10)))
- '****** 產品(A:J)欄位資料導入陣列 ****
- '1:產品欄位週別 ,2'產品欄:更新週別,3:MP date,4:產品類別,5:PRODUCT ID,
- '6:CHILDPARTNUMBER,7:CHILD_DESCRIPTION,8:Maker,9:MAKER & CODE.10:ID & PartNumber
- d.Add AR, .Range("J" & i) '
- '*****找出[產品管控清單]重複的[ID & PartNumber] ****
- If Err <> 0 Then
- If Rng Is Nothing Then
- Set Rng = .Range("J" & i)
- Else
- Set Rng = Union(.Range("J" & i), Rng)
- End If
- End If
- Err.Clear
- '*****************************************************
- Next
- End With
- On Error GoTo 0 '不再處裡程式的錯誤
- If Not Rng Is Nothing Then Rng.EntireRow.Delete
- With Worksheets("物料管控清單")
- .UsedRange.Offset(1).Clear
- For i = 1 To d.Count
- With .Range("A" & i + 1)
- '產品欄位
- '1:產品欄位週別 ,2'產品欄:更新週別,3:MP date,4:產品類別,5:PRODUCT ID,
- '6:CHILDPARTNUMBER,7:CHILD_DESCRIPTION,8:Maker,9:MAKER & CODE.10:ID & PartNumber
- .Range("A1") = d(i)(5) '導入物品欄位A1-M1
- .Range("B1") = d(i)(6)
- .Range("C1") = d(i)(7)
- .Range("D1") = d(i)(8)
- .Range("E1") = d(i)(9)
- .Range("F1") = d(i)(10)
- .Range("G1") = Format(d(i)(3), "YYYY/M/D")
- .Range("H1") = d(i)(2)
- .Range("I1") = d(i)(1)
- .Range("M1") = DateDiff("d", Date, .Range("G1")) '工作日(M)
- End With
- Next
- End With
- MsgBox d.Count & "項 OK"
- End Sub
|
|
發表於 2014-1-24 11:19
| 
