- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
2#
發表於 2016-1-26 19:45
| 只看該作者
本帖最後由 c_c_lai 於 2016-1-26 20:00 編輯
回復 1# 藍天麗池
在 shtRTD (RTD) 表單內 RecordPrice()
加入 STSumifs WR, WR， 如下：- Option Explicit
- Sub RecordPrice()
- Dim WR As Long
- Dim I As Byte
- If Range("P2") < 1 Then Exit Sub
- WR = Range("A1").End(xlDown).Row + 1
- ' ActiveWindow.ScrollRow = WR - 5 ' 只顯示最新幾筆資料
- If (WR = 3) Or _
- (Range("F" & WR - 1) <> Range("F2")) Then ' 總量有異動時才記錄
- For I = 1 To 6
- Cells(WR, I) = Cells(2, I)
- Next 'I
-
- STSumifs WR, WR ' 資料同步將數值寫入到 R，S，T 三欄內
- End If
- ' With ActiveWindow
- ' If Intersect(Cells(WR, "B"), .VisibleRange) Is Nothing Then .SmallScroll 5
- ' End With
- End Sub
- Private Function STSumifs(ByVal endST As Long, Optional startST As Long = 3)
- Dim cts As Long
- Dim btm As Long
-
- btm = Range("A1").End(xlDown).Row
-
- For cts = startST To endST
- Cells(cts, "R") = IIf(cts = 2, "", IIf(cts = 3, "=MAX(B3:B" & btm & ")", "=R" & (cts - 1) & "-1"))
- Cells(cts, "R") = Cells(cts, "R").Value
- Cells(cts, "S") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
- Cells(cts, "S") = Cells(cts, "S").Value
- Cells(cts, "T") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
- Cells(cts, "T") = Cells(cts, "T").Value
- Next cts
- End Function
- Sub Test()
- Dim WR As Long
-
- WR = Range("A1").End(xlDown).Row
- STSumifs WR
- End Sub
R，S，T 三欄的資料值。如果不異動的話，就不需執行它。
其它程式部分均保留原貌。因我無券商連線無法測知狀況。 |
|
發表於 2016-1-26 21:29
| 
