- 帖子
- 234
- 主題
- 19
- 精華
- 0
- 積分
- 276
- 點名
- 0
- 作業系統
- Windows XP
- 軟體版本
- office 2003
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2013-1-7
- 最後登錄
- 2021-10-7
|
本帖最後由 jcchiang 於 2020-10-21 13:50 編輯
回復 1# fangsc
試試看!
將"N90122購貨明細貼上"內符合"菸架"內名單&產品代號"1"開頭的數量做統計
Sub ex()
Dim d As Object
Dim arr As Object
Dim x%, AA$
Set d = CreateObject("Scripting.Dictionary")
Set arr = Sheets("N90122購貨明細貼上").[a1].CurrentRegion
For x = 2 To arr.Rows.Count
AA = arr(x, 3) & "-" & Left(arr(x, 1), 1)
If Not d.exists(AA) Then
d.Add AA, arr(x, 6)
Else
d(AA) = d(AA) + arr(x, 6)
End If
Next
Set arr = Sheets("菸架").[a1].CurrentRegion
For x = 2 To arr.Rows.Count
For Each a In d
If a = arr(x, 1) & arr(x, 2) & "-1" Then arr(x, 4) = d(a): GoTo Line1
Next
arr(x, 4) = 0
Line1:
Next
End Sub |
|
發表於 2020-10-21 13:43
| 