- 帖子
- 44
- 主題
- 1
- 精華
- 0
- 積分
- 78
- 點名
- 0
- 作業系統
- WIN XP
- 軟體版本
- SP3
- 閱讀權限
- 20
- 性別
- 男
- 來自
- taipei
- 註冊時間
- 2012-5-4
- 最後登錄
- 2014-4-24
|
回復 30# GBKEE
GBKEE大大,我剛剛執行程式,發現小計算出的數值都不正確,請問要修改哪裡?不好意思再麻煩您幫忙,謝謝您- Option Explicit
- Private Sub 更新()
- With ActiveSheet
- .Cells.Clear
- With .QueryTables.Add("URL;http://www.taifex.com.tw/chinese/3/3_2_2.asp", ActiveSheet.[A1])
- .WebFormatting = xlWebFormattingNone
- .Refresh BackgroundQuery:=False
- ActiveSheet.Names(.Name).Delete
- End With
- .Range("E:G,I:L,N:Q").Delete '刪除多餘的欄
- .Range("1:6,8:8").Delete '刪除多餘的列
- .Range("B1").End(xlDown).Offset(1).Resize(2).EntireRow.Delete '刪除多餘的列
- .Range("A:A").Insert '插入一欄
- .[B1].Resize(, 12) = Array("契約", "月份", "履約價", "買賣權", "成交價", "未平倉量", "CALL", "=C2", "call-oi", "put-oi", "call-oi$", "put-oi$")
- '** "=C2" 可修改為 正確的參照 ***
- With .Range("b2", .[b2].End(xlDown))
- .Offset(, -1) = "=rc4 +rc8 + rc9"
- .Columns(5).Replace "-", ""
- .Columns(7) = "=IF(rc[-3]=""Call"",1,0)" 'R1C1表示法 : 工作表上腧入公式
- .Columns(8) = "=IF(rc[-6]=r1c9,1,8)"
- .Columns(9) = "=IF(rc[-2]=1,rc[-3],0)"
- .Columns(10) = "=IF(rc[-3]=0,rc[-4],0)"
- .Columns(11) = "=if(rc[-1]=0,rc[-6]*rc[-5],"""")"
- .Columns(12) = "=if(rc[-2]<>0,rc[-7]*rc[-6],"""")"
- End With
- .UsedRange.Value = .UsedRange.Value '消除公式
- .Columns.AutoFit
- With .Range("b2", .[b2].End(xlDown))
- .Offset(, -1) = "=rc4 +rc8 + rc9"
- .Columns(5).Replace "-", ""
- .Columns(7) = "=IF(rc[-3]=""Call"",1,0)" 'R1C1表示法 : 工作表上腧入公式
- .Columns(8) = "=IF(rc[-6]=r1c9,1,8)"
- .Columns(9) = "=IF(rc[-2]=1,rc[-3],0)"
- .Columns(10) = "=IF(rc[-3]=0,rc[-4],0)"
- .Columns(11) = "=if(rc[-1]=0,rc[-6]*rc[-5],"""")"
- .Columns(12) = "=if(rc[-2]<>0,rc[-7]*rc[-6],"""")"
- With .Cells(.Rows.Count + 1, 1) '.Rows.Count + 1 範圍內資料總列數+1
- .Cells(1, 0) = "小計"
- .Cells(1, 6) = Application.Sum(.Parent.Columns(6))
- .Cells(1, 9) = Application.Sum(.Parent.Columns(9))
- .Cells(1, 10) = Application.Sum(.Parent.Columns(10))
- .Cells(1, 11) = Application.Sum(.Parent.Columns(11))
- .Cells(1, 12) = Application.Sum(.Parent.Columns(12))
- End With
- End With
- End With
- End Sub
|
|
發表於 2012-10-18 20:34
| 
