- 帖子
- 1018
- 主題
- 15
- 精華
- 0
- 積分
- 1058
- 點名
- 0
- 作業系統
- win7 32bit
- 軟體版本
- Office 2016 64-bit
- 閱讀權限
- 50
- 性別
- 男
- 來自
- 桃園
- 註冊時間
- 2012-5-9
- 最後登錄
- 2022-9-28
|
回復 1# freeffly - Sub 銷貨公式()
- Application.ScreenUpdating = False
- nC = Sheets("銷貨").Range("A4").End(xlToRight).Offset(, 1).Column
- nR = Sheets("銷貨").Range("A65536").End(xlUp).Row
- nC1 = Sheets("銷貨").Range("A4").End(xlToRight).Offset(, 1).Column - 2
- x = [iv1].End(xlToLeft).Column - 21
-
- With Sheets("全部")
- .Range("V2").Resize(1, x).FormulaR1C1 = "=GETPIVOTDATA(""本幣銷貨金額"",銷貨!R3C1,""月份"",R1C)"
- .Range("V9").Resize(1, x).FormulaR1C1 = "=SUMPRODUCT((銷貨!R5C" & nC & ":R" & nR & "C" & nC & "=""營業課"")*(銷貨!R4C3:R4C" & nC1 & "=R1C),銷貨!R5C3:R" & nR & "C" & nC1 & ")"
- .Range("V4").Resize(1, x).FormulaR1C1 = "=GETPIVOTDATA(""合計未收帳款"",帳齡!R1C1,""月份"",R1C)"
-
- For Each area In .Range("V7,V14,V21").Areas
- area.Resize(, x).FormulaR1C1 = "=VLOOKUP(R1C20,單頭資料!C1:C45,45,0)"
- Next
- For Each area In .Range("V16,V18").Areas
- area.Resize(, x).FormulaR1C1 = "=R[-14]C-R[-7]C"
- Next
- End With
- End Sub
|
|
發表於 2013-6-19 17:05
| 
