- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
39#
發表於 2016-4-9 08:08
| 只看該作者
本帖最後由 GBKEE 於 2016-4-10 14:28 編輯
回復 37# 藍天麗池 - Option Explicit
- Private Sub Workbook_Open()
- Dim xRng As Range, First_Aaddres As String, xRng_Name As String
- Application.Calculation = xlCalculationAutomatic
- With Sheets("RTD")
- Set xRng = .Rows(2).Cells.Find("TotalVolume", LookIn:=xlFormulas)
- If Not xRng Is Nothing Then
- First_Aaddres = xRng.Address
- Do
- xRng_Name = Split(xRng.Formula, "'")(1)
- xRng_Name = "TotalVolume" & Split(xRng_Name, ".")(0)
- .Names.Add xRng_Name, xRng ''名稱定義:所有總量的Dee公式的儲存格
- Set xRng = .Rows(2).Cells.FindNext(xRng)
- Loop Until xRng.Address = First_Aaddres
- End If
- End With
- End Sub
- Option Explicit
- Private Sub Worksheet_Calculate()
- Dim Rng As Range, E As Variant
- On Error Resume Next ' 檔案開啟時 DEE傳回錯誤值
- Set Rng = UsedRange.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
- If Not Rng Is Nothing Then Exit Sub
- On Error GoTo 0 '開盤不再處理程式碼的錯誤
- If Time < #9:00:00 AM# Or Time > #1:31:00 PM# Then Exit Sub '非營業時間
- For Each E In Me.Names
- If E.Name Like "*TotalVolume*" Then '總量的名稱
- If Range(E.Name) > 0 Then
- With Cells(Rows.Count, Range(E.Name).Column).End(xlUp) ''總量名稱所在的最底列往上到有資料的儲存格
- If .Row = 2 Or .Row > 2 And .Value <> Range(E.Name).Value Then
- .Offset(1).Cells(1, -2).Resize(, 4) = Range(E.Name).Cells(, -2).Resize(, 4).Value
- End If
- End With
- End If
- End If
- Next
- End Sub
|
|
發表於 2016-4-8 12:16
| 
