- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
2#
發表於 2013-7-27 12:35
| 只看該作者
回復 1# yliu
試試看- Option Explicit
- Sub EX_1() '表1的程式
- Dim 訂單號碼 As String, 請購單號 As String, 請購廠商 As String
- Dim AR(1 To 6), i As Integer
- With Sheets("report")
- 訂單號碼 = .[B5]
- 請購單號 = .[D5]
- 請購廠商 = .[B6]
- With Sheets("final")
- i = 2
- Do While .Cells(i, "A") <> ""
- If .Cells(i, "v").Value = 訂單號碼 And .Cells(i, "F") = 請購單號 And .Cells(i, "L") = 請購廠商 Then
- AR(1) = .Cells(i, "j") '品名
- AR(2) = AR(2) & IIf(AR(2) = "", "", ",") & .Cells(i, "k") '規格
- AR(3) = .Cells(i, "i") '會計科目
- AR(4) = .Cells(i, "b") '採購性質
- AR(5) = AR(5) + .Cells(i, "m") 'User請購價
- AR(6) = .Cells(i, "c") '採購類別
- End If
- i = i + 1
- Loop
- End With
- .[B7] = AR(1)
- .[B8] = AR(2)
- .[B9] = AR(3)
- .[D9] = AR(4)
- .[B10] = AR(5)
- .[D10] = AR(6)
- End With
- End Sub
- Sub EX_2() '表1的程式
- Dim d As Object, KEY, i As Integer, AR(), A(1 To 4), 訂單號碼 As String
- Set d = CreateObject("SCRIPTING.DICTIONARY") '字典物件 Scripting.Dictionary
- 訂單號碼 = Sheets("report").[B5]
- With Sheets("record")
- .AutoFilterMode = False '取消 工作表的自動篩選
- i = 2
- Do While .Cells(i, "A") <> ""
- If .Cells(i, "P").Value = 訂單號碼 Then '比對 訂單號碼
- If d.exists(.Cells(i, "C").Value) = False Then d(.Cells(i, "C").Value) = .Cells(i, "C")
- End If
- i = i + 1
- Loop
- ReDim AR(1 To d.Count) '重置陣列的元素上限=字典物件的計數(Count)
- .Range("A1").AutoFilter 16, 訂單號碼 'AutoFilter 自動篩選 第16欄 指定條件值=訂單號碼
- i = 1
- For Each KEY In d.KEYS
- .Range("A1").AutoFilter 3, KEY 'AutoFilter 自動篩選 第3欄 指定條件值=字典物件的KEY
- A(1) = KEY '採購廠商
- A(2) = Application.Sum(.Range("M:M").SpecialCells(xlCellTypeVisible)) '第1次議價
- A(3) = Application.Sum(.Range("N:N").SpecialCells(xlCellTypeVisible)) '第2次議價
- A(4) = Application.Sum(.Range("O:O").SpecialCells(xlCellTypeVisible)) '第3次議價
- AR(i) = A
- i = i + 1
- Next
- End With
- Sheets("report").[B13].Resize(4, 3) = Application.WorksheetFunction.Transpose(AR) '導入
- End Sub
|
|
發表於 2013-7-26 22:19
| 
