- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
48#
發表於 2012-10-25 20:48
| 只看該作者
回復 47# usana642
試試看- Option Explicit
- Sub 儲存小計結果()
- Dim Rng(1 To 4) As Range, AR(1 To 6), xi As Integer, e As Variant
- With Sheets("計算")
- Set Rng(1) = .Range("G1").End(xlDown) 'G1往下最後有資料的儲存格
- Set Rng(2) = .Range("E2", .[E2].End(xlDown)) '買賣權
- End With
- For Each e In Array("Call", "Put")
- Rng(2).Replace e, "=usana642" '公式不存在 傳回錯誤值
- With Rng(2).SpecialCells(xlCellTypeFormulas, xlErrors) '有錯誤的儲存格
- With .Offset(, 2) '右移2欄
- xi = IIf(e = "Call", 0, 1)
- Set Rng(3) = .Find(Application.Max(.Cells)) '尋找最大值
- AR(1 + xi) = Rng(3).Offset(, -3) '履約價
- AR(3 + xi) = Rng(3).Offset(, -1) '最後成交價
- AR(5 + xi) = Rng(3) '未沖銷契約量
- End With
- .Value = e
- End With
- Next
- With Sheets("紀錄").Cells(Rows.Count, "A").End(xlUp).Cells(2, 1)
- 'Cells(Rows.Count, "A").End(xlUp):A欄最後列往上有資料的儲存格.Cells(2, 1) :第2列 ,第1欄
- .Value = Date
- .Cells(1, 2) = Rng(1)
- .Cells(1, 3).Resize(1, 4) = Rng(1).Cells(1, 4).Resize(1, 4).Value
- .Cells(1, 7).Resize(1, 6) = AR
- End With
- End Sub
|
|
發表於 2012-10-21 21:34
| 
