- 帖子
- 2839
- 主題
- 10
- 精華
- 0
- 積分
- 2895
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2025-2-15
|
回復 77# PJChen
不研究公式是對或錯, 也沒法驗證, 照抄!!! 若有誤自行去修改
Sub 理貨訂購量()
Dim Rw&, xR As Range, xD, xH As Range, c$, Fx$
Rw = Cells(Rows.Count, "K").End(xlUp).Row
If Rw <= 2 Then Exit Sub
Set xD = CreateObject("Scripting.Dictionary")
xD("全都") = "=SUMIFS(網單.全都!$I:$I,網單.全都!$C:$C,BF理貨!$D2," & _
"網單.全都!$K:$K,BF理貨!$C2)+IF(BF理貨!$R$283=BF理貨!$B$283,BF理貨!$R2,0)"
xD("統統") = "=SUMIFS(網單.統統!$R:$R,網單.統統!$M:$M,BF理貨!$D2,網單.統統!$AC:$AC,BF理貨!$C2," & _
"網單.統統!$AE:$AE,BF理貨!$B$1)+IF(BF理貨!$R$1=BF理貨!$B$1,BF理貨!$R2,0)"
xD("德QQK") = "=SUMIF(網單.德QQK!$E:$E,BF理貨!$D2,網單.德QQK!$G:$G)+IF(BF理貨!$R$388=BF理貨!$B$388,BF理貨!$R2,0)"
xD("M社") = "=SUMPRODUCT((網單.M社!$R$2:$R$300=BF理貨!$D2)*(網單.M社!$AP$2:$AP$300))+" & _
"IF(BF理貨!$R$561=BF理貨!$B$561,BF理貨!$R2,0)"
xD("得來") = "=SUMIFS(網單.得來!$L:$L,網單.得來!$H:$H,BF理貨!$D2,網單.得來!$O:$O,BF理貨!$C2)" & _
"+IF(BF理貨!$R$420=BF理貨!$B$420,BF理貨!$R2,0)"
xD("W康") = "=SUMPRODUCT((網單.W康!$C$6:$C$298=BF理貨!$D2)*(網單.W康!$D$6:$D$298))+" & _
"IF(BF理貨!$R$508=BF理貨!$B$508,BF理貨!$R2,0)"
For Each xR In Range("K2:K" & Rw)
If xR = "品名" Then Set xH = xR(2, 7): c = Range("A" & xR.Row): GoTo 101
If xR = "合計" Then
Fx = xD(c)
If c = "" Or Fx = "" Then GoTo 101
[Q2].Formula = Fx
With Range(xH, xR(0, 7)) 'Q欄填入公式
.FormulaR1C1 = [Q2].FormulaR1C1
.Value = .Value
.Replace 0, "", 1 '*****(1,完全符合)
End With
c = ""
End If
101: Next
[Q2] = "訂購數"
End Sub
====================================== |
|
發表於 2020-6-23 13:45
| 
