作者ceendy (啊嗚~)
看板Visual_Basic
標題[VBA ] Clear Range的問題
時間Wed Mar 29 03:38:29 2006
我其實沒有很懂 VB 程式碼 是初學者 拜託前輩指教一下
這個VBA程式 是要篩選資料的
把每天的股價資料讀進來後 篩完就把這天資料clear掉
但是在按clear鍵後 800行之後的資料都無法刪除 超怪的
請高手們幫我看一下 謝謝了!!!
Private Sub clear_Click()
ThisWorkbook.Sheets("temp").Range("rawdata").ClearContents
ThisWorkbook.Sheets("final").Range("rec").ClearContents
End Sub
Private Sub CommandButton1_Click()
Set daterange = ThisWorkbook.Sheets("para").Range("daterange")
Application.Calculation = xlCalculationManual
ThisWorkbook.Sheets("temp").Range("rawdata").ClearContents
Set stkrange = ThisWorkbook.Sheets("para").Range("stkrange")
Set daterange = ThisWorkbook.Sheets("para").Range("daterange")
Set rawdata = ThisWorkbook.Sheets("temp").Range("rawdata")
Set rec = ThisWorkbook.Sheets("final").Range("rec")
b = 1
While daterange.Cells(b, 1) <> ""
b = b + 1
Wend
c = 1
While stkrange.Cells(c, 1) <> ""
c = c + 1
Wend
num = 1
While rec.Cells(num, 1) <> ""
num = num + 1
Wend
For i = 1 To b - 1
For j = 1 To c - 1
ThisWorkbook.Sheets("temp").Range("rawdata").ClearContents
With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\nsefo\" & daterange(i, 1) & ".bhv" _
, Destination:=Sheets("temp").Range("A1"))
.Name = daterange(i, 1)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
'ActiveWorkbook.Names("_" & daterange(i, 1)).Delete
a = 1
While rawdata.Cells(a, 1) <> ""
a = a + 1
Wend
For k = 1 To a - 1
If rawdata.Cells(k, 2) = stkrange(j, 1) And rawdata.Cells(k, 3) = "FUTSTK" Then
rec.Cells(num, 1) = rawdata.Cells(k, 1)
rec.Cells(num, 2) = rawdata.Cells(k, 2)
rec.Cells(num, 3) = rawdata.Cells(k, 3)
rec.Cells(num, 4) = rawdata.Cells(k, 4)
rec.Cells(num, 5) = rawdata.Cells(k, 5)
rec.Cells(num, 6) = rawdata.Cells(k, 6)
rec.Cells(num, 7) = rawdata.Cells(k, 7)
rec.Cells(num, 8) = rawdata.Cells(k, 8)
rec.Cells(num, 9) = rawdata.Cells(k, 9)
rec.Cells(num, 10) = rawdata.Cells(k, 10)
rec.Cells(num, 11) = rawdata.Cells(k, 11)
rec.Cells(num, 12) = rawdata.Cells(k, 12)
rec.Cells(num, 13) = rawdata.Cells(k, 13)
rec.Cells(num, 14) = rawdata.Cells(k, 14)
rec.Cells(num, 15) = rawdata.Cells(k, 15)
rec.Cells(num, 16) = rawdata.Cells(k, 16)
'rec.Cells(num, 17) = rawdata.Cells(k, 17)
num = num + 1
End If
Next k
Next j
Next i
Application.Calculation = xlCalculationManual
End Sub
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.167.74.137
1F：推 sueadolph:從頭到尾沒看到 Range("rawdata")的定義..... 03/31 02:29
2F：→ sueadolph:這部份可以在EXCEL檔案上頭先行設定 03/31 02:30
3F：→ sueadolph:請先看一下你的EXCEL檔 有沒有區域叫 rawdata的 03/31 02:30
4F：→ sueadolph:然後....某些錄製後..不影響結果的多餘程式碼 請刪除= = 03/31 02:31
5F：→ sueadolph:哦~對了rawdata的找法是..在EXCEL的fx 左邊有個小箭頭 03/31 02:33
6F：→ ceendy:感謝你 我有發現到 哈哈 的確沒定義這個 thank you 03/31 22:42
7F：→ ceendy:呵 我會好好練習寫程式的好習慣吧 :p 03/31 22:42