(不定期更新)使用VBA解決 excel web 查詢無法匯入、匯入太慢的股市資料

在懂得有限的指令裡,我用錯誤+條件混合判斷,把IF條件減少到了3個,

可是我覺得自己的邏輯是有問題的,應該是要先處理數據源,然後在計算時用什麼方法跳過空值及0,

不然每次計算都要判斷好像很傻,若是更複雜的公式,那判斷條件又要增加,

想請教版主大神,還有沒有什麼更好的方式。

[點擊下載]
感謝您的提問,這樣大家(我)又有機會學習一下樓(版)主的高深技巧。

不過樓主好像還沒時間上來......我先請教一下,您要的結果,是只出現最後一列,其他都不要?

如果是這樣的話,其實您第一個[跳過錯誤方法]應該是可行的啊?



上面是我試著幫您小修一下[跳過錯誤方法]程式的結果....修改的程式碼如下:

Sub TEST1() '測試結果(跳過錯誤方法)

On Error Resume Next '執行時跳過錯誤(預防單元格內,有無法判斷的的字串)
Range("E3:E12") = "" '先清空E3:E12原本資料,以確定本次執行的真正結果
'上面這句不執行也OK,因後續整個E3:E12都會被Arr3的內容覆蓋

Arr1 = Range("B3:B12")
Arr2 = Range("C3:C12")
Arr3 = Range("D3:D12") '因為D3:D12是空白,讓Arr3一開始也全指定空白,而不是像Arr1,先接收了B3:B12的值

For i = 1 To UBound(Arr1, 1) 'i = 多少行
' Arr1(i, 1) = Arr1(i, 1) / Arr2(i, 1) 把原本的執行註記,先不執行
If Abs(Arr1(i, 1) * Arr2(i, 1)) > 0 Then Arr3(i, 1) = Arr1(i, 1) / Arr2(i, 1) '若無錯誤,把結果寫到Arr3
Next

'Range("E3:E12") = Arr1 --這句註記,先不執行
Range("E3:E12") = Arr3 '把Arr3 寫到目標格

On Error GoTo 0 '重設錯誤機制

End Sub

我盡量加上註解,也只是微調,基本是都是用您原來的程式碼。

如果這不是您要的,或是覺得效率不夠,也沒關係,就請您稍微耐心等候一下,樓主的回覆囉。
Der,misser1
感謝misser高手,您的Abs(Arr1(i, 1) * Arr2(i, 1)) > 0高明多多了,判斷語句省到只剩下1個,我試了公式多加1個參數,這種判斷方式Abs(Arr1(i, 1) * Arr2(i, 1) * Arr3(i, 1)) > 0也沒問題,我的思維真是太直線了,再次感謝您的指導。
misser wrote:
有機會學習一下樓(版)主的高深技巧。


不好意思啊,根據Dylan67的檔案,裡面只有除法
加、減、乘、除…沒什麼特殊技巧,讓您失望了


Dylan67 wrote:
不然每次計算都要判斷好像很傻,若是更複雜的公式,那判斷條件又要增加


如果照您檔案內的資料格式,其實您test3和misser 這位高手提供的另一種寫法是一樣快的
影響您程式速度的瓶頸,應該不是這個檔案中的除法

(20000-2筆,測試)




稍微修正一下您的test3,因為有on error ,可偷懶少用一些判斷、計算,可再加快一點點
不過還是建議,寫足判斷、計算,將來除錯上比較方便

Sub TEST3()

Dim Arr3(1 To 20000, 1 To 1), i As Integer, ttt As Double
On Error Resume Next

ttt = Timer
arr1 = Range("B3:C20000")

For i = 1 To UBound(arr1, 1)
If arr1(i, 1) <> 0 Then Arr3(i, 1) = arr1(i, 1) / arr1(i, 2)
Next

Range("K3:K20000") = Arr3

On Error GoTo 0
Debug.Print Timer - ttt

End Sub
感恩snare版主及misser高手的啟發,

這兩天檢視手上越來越[臃腫]的程序,發現經常為了求解,用了好多的Error,

也造成有數據源分析的假象,及不正確值的災難,改起來1個頭5個大,暈倒。另想請教

.Range("AA100").Activate '選取空白單元格作為粘貼定位點

.PasteSpecial NoHTMLFormatting:=True '用指定格式將剪貼簿的內容貼到工作表上

這兩小段程序碼在[不隱藏工作表]時可以執行,但在[隱藏工作表]時不能執行,還請各位先進指導。
Dylan67 wrote:
.Range("AA100").Activate '選取空白單元格作為粘貼定位點

.PasteSpecial NoHTMLFormatting:=True '用指定格式將剪貼簿的內容貼到工作表上


?這問題.......嗯,太「簡單」的方法,您不想考慮嗎?

Sheets(1).Visible = -1 '解除隱藏

Sheets(1).Select '選定 (後面的xx格.Activate 才不會出錯)

.............. '(您想處理的步驟)

Sheets(1).Visible = 2 '深度隱藏(也可以用=0,一般隱藏)


打開(顯示).....(處理).....上鎖(隱藏) 三部曲,不就OK?
(我猜事情沒有我想的這麼「簡單」,哈哈)
Der,misser1
misser,感謝您的回覆,太[簡單]的方法也是學習,受教了,

只是我已經這麼[擁腫]了,這個方式[減肥]其實是大工程,

那就再請教個[簡單]問題,以往跨行賦值我都是先copy成連續列,再賦給一個變量,

要不就是(i,j)一個一個寫入,

今天突發奇想也許可以這樣做 Arr = Union(Range("A1:B5"), Range("D1:E5")),

可是不成功,您有什麼好方法嗎?
Dylan67 wrote:
Arr = Union(Range("A1:B5"), Range("D1:E5"))


不錯啊,這招好。......但是,您的語法可能要小修一下?

使用VBA聯合方法
當您運行此宏時,範圍 A1:C4 和 E1:F4 合併為一個範圍,我們將其存儲在變量中 ng1。注意我們如何使用 關鍵字將統一範圍設置為變量Set ng1。 沒有關鍵字,您無法將合併範圍分配給變量。


上面是某網頁的中文翻譯(網址下附),所以,您可能得先定義清楚,包含用 Set 來執行那句「Arr=.........」

來個簡單的範例(也是修改自該網頁):
(請先在A1:A5、B1:B5、D1:D5、E1:E5先打一點資料)

Sub BasicUnionDemo2()
Dim rng1 As Range
Dim item As Range
Set rng1 = Union(Range("A1:B5"), Range("D1:E5"))

For Each item In rng1
Debug.Print item.Value
Next item
End Sub

網址:
https://wellsr.com/vba/2019/excel/use-vba-union-to-combine-ranges/

所以您的設想,看來應該是可行的............您研究看看?

若要更清楚說明的話,可能要等看看s大是不是有時間,來「開示」大家囉。
Der,misser1
謝謝熱心的misser,我後來也Set了,不過好像不是我要的(必須Dim成Range),

我的困惑大概類似[單位換算]情況,

B1:C5計算完粘貼到A1:B5,然後E1:F5計算完粘貼到C1"D5,

然後如果有很多不連續列範圍都是相同的行數,以及相同的簡單公式,

那麼我就會一直重複下面的程序,直到開始覺得有點不太對勁,應該有更聰明的方法吧,哈哈

Sub TEST()

Arr = Range("B1:C5")
For i = 1 To UBound(Arr, 1) '1 = 多少行
For j = 1 To UBound(Arr, 2) '2 = 多少列
Arr(i, j) = Arr(i, j) / 1000
Next
Next

Range("E1:F5") = Arr
Arr = Range("E1:F5")
For i = 1 To UBound(Arr, 1) '1 = 多少行
For j = 1 To UBound(Arr, 2) '2 = 多少列
Arr(i, j) = Arr(i, j) / 1000
Next
Next
Range("C1:D5") = Arr

End Sub
Dylan67 wrote:
.Range("AA100").Activate '選取空白單元格作為粘貼定位點
.PasteSpecial NoHTMLFormatting:=True '用指定格式將剪貼簿的內容貼到工作表上
這兩小段程序碼在[不隱藏工作表]時可以執行,但在[隱藏工作表]時不能執行,還請各位先進指導。


只有excel內容的複製,貼上時才可以不用先select、activate,可直接指定位置
不會因為目地是隱藏工作表而出錯
Sheets("工作表1").Range("a1").Copy
Sheets("隱藏工作表").Range("b1").PasteSpecial Paste:=xlPasteValues

excel外部,記事本、ie…等等,需先select、activate一個位置,才能正確貼上
請使用misser所教的方法處理,或改用一格一格(陣列)寫入
但可加上
Application.ScreenUpdating = False
……程式碼…
Application.ScreenUpdating = True
避免看到excel工作表切換

不過,隱藏資料表,似乎沒有多大的意義


Dylan67 wrote:
我的困惑大概類似[單位換算]情況,
B1:C5計算完粘貼到A1:B5,然後E1:F5計算完粘貼到C1"D5

有很大的問題,除了計算結果和資料區重疊,最後的結果是除1000000 ???

只是換算不需vba,可用函數處理 b1=a1/1000

硬要用vba,也可同一格覆寫,貼來貼去的只會造成資料錯亂
for i=1 to 5
cells(i,1)=cells(i,1)/1000
next i

利用union同時處理多range,是很好用
但從您程式碼看來,您似乎會把資料不小心混在一起
使用上需更加小心

Sub test()
Dim Union_Range As Range, temp As Range
Set Union_Range = Union(Range("a1:b5"), Range("d1:e5"))

For Each temp In Union_Range
temp.Value = temp.Value / 1000
Next
End Sub



另外再提醒一次on error,在寫程式碼的過程中,建議不要用
除非所有的錯誤都是掌握之中,直到程式碼完成
文章分享
評分
評分
複製連結
請輸入您要前往的頁數(1 ~ 160)

今日熱門文章 網友點擊推薦!