- 帖子
- 2834
- 主題
- 10
- 精華
- 0
- 積分
- 2890
- 點名
- 0
- 作業系統
- 〔略〕
- 軟體版本
- 〔略〕
- 閱讀權限
- 100
- 性別
- 男
- 來自
- 〔略〕
- 註冊時間
- 2013-5-13
- 最後登錄
- 2024-11-25
|
13#
發表於 2020-3-20 13:14
| 只看該作者
Sub 公式_01()
Dim xR As Range, xH As Range, xA As Range, C%, N&
Set xR = [C5] '第一區定位格
C = Application.Match("合計", Rows(4), 0) - xR.Column '欄位數
Set xA = xR.Resize(7, C) '第一區(不含合計欄)
Application.ScreenUpdating = False
xA = "=IF(C4=0,"""",INT(C4/$C$3)&""箱""&TEXT(MOD(C4,$C$3),""+0;;;""))"
xA.Rows(1) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$AP$3:$BH$3=C4)*(飛比!$AP$4:$BH$70))"
xA.Rows(3) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$BJ$3:$CB$3=C4)*(飛比!$BJ$4:$CB$70))"
xA.Rows(5) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CD$3:$CV$3=C4)*(飛比!$CD$4:$CV$70))"
xA.Rows(6) = "=SUMPRODUCT((飛比!$F$4:$F$70=$B$3)*(飛比!$CX$3:$DP$3=C4)*(飛比!$CX$4:$DP$70))"
xR(1, C + 1).Resize(7) = "=IF($B5=""箱+瓶"","""",SUM(" & xA.Rows(1).Address(0, 0) & "))" '合計欄
'-----------------------------------------------
Set xA = xR.Resize(7, C + 1) '第一區(含合計欄)
Do
N = N + 1: Set xH = xR(N * 9 + 1, 1)
If xH(1, 0) <> "訂購數" Then Exit Do
With xH.Resize(7, C + 1)
xA.Copy .Cells
.Value = .Value
End With
Loop
xA.Value = xA.Value
End Sub
 Xl0000048.rar (35.95 KB)
====================================== |
|
發表於 2020-3-9 08:54
| 
