- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 1# nifanliu
檔案關閉前 執行- Sub Ex() '公式轉為值:公式減少,重算儲存格時間縮短
- Dim R As Integer
- With Sheets("Shipping_PO").UsedRange 'Shipping_PO
- For Each e In .Columns
- Names.Add Chr(64 + e.Column) & "欄", e.Offset(1).Address(, , , 1)
- '名稱定義 為 A欄,B欄,,C欄,D欄,E欄
- Next
- End With
-
- With Sheets("PO")
- .Activate
- [I2].Select
- Names.Add "IA", "=!RC[-8]" '名稱定義 相對 [I2] 於 -8欄 的位置
- [I2].Select
- Names.Add "IC", "=!RC[-6]" '名稱定義 相對 [I2] 於 -6欄 的位置
- [L2].Select
- Names.Add "LA", "=!RC[-11]"
- [L2].Select
- Names.Add "LC", "=!RC[-9]"
- R = .[a1].End(xlDown).End(xlDown).End(xlUp).Row
- If R = 1 Then R = 2
- .Range("F2:F" & R) = "=RC[-1]/RC[-2]" '=E2/D2
-
- .Range(" G2:G" & R) = "=RC[-1]/(VALUE(LEFT(RC[-4],2))*(VALUE(RIGHT(RC[-4],2)))*(0.0254^2))"
- '=F2/(VALUE(LEFT(C2,2))*(VALUE(RIGHT(C2,2)))*(0.0254^2))
-
- .Range("H2:H" & R) = "=RC[-2]*29.5*1000" '=F2*29.5*1000
-
- .Range("I2:I" & R) = "=SUMPRODUCT((" & [B欄] & "=IA)*(" & [D欄] & "=IC)," & [E欄] & ")"
- '=SUMPRODUCT((Shipping_PO!$A$2:$A$65=A3)*(Shipping_PO!$D$2:$D$65=C3),Shipping_PO!$E$2:$E$65)
-
- .Range("J2:J" & R) = "=RC[-6]-RC[-1]" '=D2-I2
-
- .Range("K2:K" & R) = "=IF(RC[-1]>0,""Open"",""Close"")" '=IF( J2>0, "Open","Close")
-
- .Range("L2:L" & R) = "=SUMPRODUCT((" & [B欄] & "=LA)*(" & [D欄] & "=LC)*(MONTH(" & [C欄] & ")=MONTH(TODAY()))," & [E欄] & ")"
- '=SUMPRODUCT((Shipping_PO!$B$2:$B$65536=A2)*(Shipping_PO!$D$2:$D$65536=C2)*(MONTH(Shipping_PO!$C$2:$C$65536)=MONTH(TODAY())),Shipping_PO!$E$2:$E$65536)
-
- .Range("M2:M" & R) = "=RC[-1]*RC[-7]" '=L2*F2"
-
- .Range("N2:N" & R) = "=RC[-1]*29.5" '=M2*29.5
-
- .Range("O2:O" & R) = "=RC[-9]*RC[-5]" '=F2*J2
-
- .Range("P2:P" & R) = "=RC[-1]*29.5" '=O3*29.5
- .UsedRange.Value = .UsedRange.Value
- .Parent.Save
- End With
- End Sub
|
|
發表於 2013-5-19 20:11
| 