- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 138
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2025-6-8
               
|
7#
發表於 2013-12-10 08:37
| 只看該作者
回復 6# jcchiang - Sub ex()
- Dim Ar(0 To 7), A As Range, C As Range, TCode, Cnt
- Ar(0) = Array("客戶代號", "客戶批號", "Icode", "輸入量", "TCode", "B1", "B1失敗", "B2", "B2失敗", "B3", "B3失敗", "B4", "B4失敗")
- Set A = Sheet2.[F:F].Find("A120004", lookat:=xlWhole)
- If Not A Is Nothing Then
- For i = -3 To 3
- TCode = A.Offset(i, 7).Value
- Set C = Sheet1.[D:D].Find(TCode, lookat:=xlWhole)
- If Not C Is Nothing And C <> "" Then b1 = C.Offset(, 4).Value Else b1 = 0
- If Not C Is Nothing And C <> "" Then b2 = C.Offset(, 6).Value Else b2 = 0
- If Not C Is Nothing And C <> "" Then b3 = C.Offset(, 8).Value Else b3 = 0
- If Not C Is Nothing And C <> "" Then b4 = C.Offset(, 10).Value Else b4 = 0
- Cnt = A.Offset(i, 4).Value
- Ar(i + 4) = Array(A.Offset(i, -5).Value, A.Offset(i, 0).Value, A.Offset(i, 2).Value, Cnt, TCode, b1, b1 / Cnt, b2, b2 / Cnt, b3, b3 / Cnt, b4, b4 / Cnt)
- Next
- End If
- With Sheet3
- .[A:M].ClearContents
- For i = 7 To 13
- .Columns(i).NumberFormat = "0.00%"
- Next
- .[A1].Resize(8, 13) = Application.Transpose(Application.Transpose(Ar))
- End With
- End Sub
複製代碼 |
|