麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 用VBA 執行COUNTIF 後，貼值及工作頁的疑問及簡化VBA [打印本頁]

作者: ppppssss 時間: 2019-5-25 22:12 標題: 用VBA 執行COUNTIF 後，貼值及工作頁的疑問及簡化VBA

有請問版上的高手。
[attach]30667[/attach]
我用錄製巨集的方式想統計出金融/電子/傳產的內外盤並做成圓餅圖想要方便觀察。
但是遇到幾個問題。
1-目的想要每10秒鐘執行巨集後運算一次然後畫圓餅圖，但巨集一執行，工作表1的ABC行的RAND 值都不會跳動了(巨集沒執行時會自已跳動)  。我也沒辨法切到其它工作表做其它事
   有辨法讓運算只在工作頁1 自已背景執行我還可以做其它儲存格的KEY IN嗎？
2-附件是想要利用VBA在$E$1 做完COUNTIF 運算後再貼"值到下方。想說這樣可以節省EXCEL 在金融/傳產/電子/內外盤12格內寫一樣的公式浪費EXCEL運算時間。
   可是錄製出來的VBA 感覺也是貼了12次一樣的運算？  可以有高手幫忙簡化嗎？
3- 因為想要在EXCEL一打開後就不管這個CONUTIF 讓它自已在工作頁1自已背景執行。 EXCEL 縮小或切換到其它的  EXCEL 檔案並不影響這個COUNTIF 工作頁的執行
   這語法還需做什麼設定嗎？還是我需要把VBA程式貼在工作表1內，然後工作表1設定成worksheet 宣告成calculation 呢？
4- 一直搞不懂巨集執行後要怎麼把巨集停止。是否有語法可以快速中斷vba？
請高手幫幫忙解答。感謝

Sub ¥¨¶°10sec()
'
'

'
'¤º¥~½L
Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-4]:R[935]C[-4],1)"
Range("E1").Copy
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-4]:R[935]C[-4],-1)"
Range("E1").Copy
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-4]:R[935]C[-4],0)"
Range("E1").Copy
Range("G5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False


'¶Ç²£
Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-3]:R[257]C[-3],1)"
Range("E1").Copy
Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-3]:R[257]C[-3],-1)"
Range("E1").Copy
Range("F7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[4]C[-3]:R[257]C[-3],0)"
Range("E1").Copy
Range("G7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

'ª÷¿Ä
Range("E1").FormulaR1C1 = "=COUNTIF(R[461]C[-3]:R[492]C[-3],1)"
Range("E1").Copy
Range("E9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[461]C[-3]:R[492]C[-3],-1)"
Range("E1").Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[461]C[-3]:R[492]C[-3],0)"
Range("E1").Copy
Range("G9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
'¹q¤l
Range("E1").FormulaR1C1 = "=COUNTIF(R[258]C[-3]:R[397]C[-3],1)+COUNTIF(R[508]C[-3]:R[562]C[-3],1)"
Range("E1").Copy
Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[258]C[-3]:R[397]C[-3],1)+COUNTIF(R[508]C[-3]:R[562]C[-3],-1)"
Range("E1").Copy
Range("F11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

Range("E1").FormulaR1C1 = "=COUNTIF(R[258]C[-3]:R[397]C[-3],1)+COUNTIF(R[508]C[-3]:R[562]C[-3],0)"
Range("E1").Copy
Range("G11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

      Call Second

End Sub

Sub Second()
'
'
Application.OnTime Now + 10 / 86400#, "¥¨¶°10sec"

End Sub

作者: GBKEE 時間: 2019-5-26 13:38

回復 1# ppppssss

工作表1的ABC行的RAND 值都不會跳動了(巨集沒執行時會自已跳動)

RAND 值都不會跳動儲存格的函數需工作表有作重算的動作,才會重做計算產生新的數值
巨集沒執行時會自已跳動是工作表中儲存格有編輯的動作,引起重算的動作

附檔可參考看看

[attach]30670[/attach]

作者: ppppssss 時間: 2019-5-26 15:45

回復 2# GBKEE

哇塞， GBKEE 大大，超乎我的想像。很感謝您的賜教。完全沒想到程式可以如此的簡潔明白。
可以再請教一段 Run ("'""" & thisworkbool.Name & "!" & 程式.巨集10sec & """'") 這個thisworkbool 跟thisworkboo"k" 會有不一樣做用嗎？
試著修改成thisworkbook 感覺程式也沒什麼影響。

這簡明的程式又要好好的研究一下了。謝謝大大。

作者: GBKEE 時間: 2019-5-27 09:06

回復 3# ppppssss
這個thisworkbool 跟thisworkboo"k" 會有不一樣做用嗎？
試著修改成thisworkbook 感覺程式也沒什麼影響。
thisworkbool VBA沒這關鍵字
ThisWorkbook 是這活頁簿模組的物件

感覺程式也沒什麼影響。附上你的檔案看看

作者: ppppssss 時間: 2019-5-27 16:16

回復 ppppssss
這個thisworkbool 跟thisworkboo"k" 會有不一樣做用嗎？
試著修改成thisworkbook 感覺 ...
GBKEE 發表於 2019-5-27 09:06

[attach]30677[/attach]

hi GBKEE 大。
   我是下載你提供的檔案想要參照的學習並修改。看到一些不懂的地方並請教。
   1-  是在 thisworkbook 的程式碼中  :Run ("'""" & thisworkbool.Name & "!" & µ{¦¡.¥¨¶°10sec & """'")
   2- 是在"表單"的userform1中有宣告option Explicit  但"程式.msg" ，如果使用巨集去執行auto_open ，會在"程式"這兩個字出現黃標 ; 然後跳出視窗說變數未定義。我是貼到我另一個導入RTD的EXCEL中出現這樣的狀況。

謝謝。
[attach]30678[/attach]

作者: GBKEE 時間: 2019-5-28 05:44

回復 5# ppppssss
你的office 是繁體版的嗎!
我給的是 Run "'" & ThisWorkbook.Name & "'!重算"
不是 Run ("'""" & thisworkbool.Name & "!" & µ{¦¡.¥¨¶°10sec & """'")

作者: ppppssss 時間: 2019-5-28 16:03

回復 ppppssss
你的office 是繁體版的嗎!
我給的是 Run "'" & ThisWorkbook.Name & "'!重算"
不是 Ru ...
GBKEE 發表於 2019-5-28 05:44

我的office 是繁體版的。
謝謝GBKEE大花時間幫忙弄了這個程式。可是這兩天把CODE COPY 進我的主程式後，發現原先想要的用意好像離題了。
因為  EXCEL 的儲存格內如果寫下公式  =COUNTIF(A5:A936，1) 也會自已算因為1;-1;0 會自已運算變化。
原先的用意想說在E5的格子用VB 的程式來運算應該是會比EXCEL 的儲存格函數運算來的快吧
所以想在E5格算完後  貼"值"  到其它儲存格內畫圖這樣會比  下方內外盤/金融/傳產 .....等  12格都有函數公式還要計算的更快上數倍。

不知這樣的想法正確嗎？

作者: GBKEE 時間: 2019-5-29 13:54

回復 7# ppppssss

Sub 巨集10sec()
Application.Calculation = xlCalculationSemiautomatic '手動重算可停止活頁簿的運算
With 工作表1
.Range("E5:G5") = Array("=COUNTIF(A5:A936,1)", "=COUNTIF(A5:A936,-1)", "=COUNTIF(A5:A936,0)")
.Range("E7:G7") = Array("=COUNTIF(B5:B258,1)", "=COUNTIF(B5:B258,-1)", "=COUNTIF(B5:B258,0)")
.Range("E9:G9") = Array("=COUNTIF(B462:B493,1)", "=COUNTIF(B462:B493,-1)", "=COUNTIF(B462:B493,0)")
.Range("E11:G11") = Array("=COUNTIF(B259:B398,1)+COUNTIF(B509:B563,1)", "=COUNTIF(B259:B398,1)+COUNTIF(B509:B563,-1)", "=COUNTIF(B259:B398,1)+COUNTIF(B509:B563,0)")
End With
Application.Calculation = xlCalculationAutomatic 程式碼執行後恢復活頁簿自動重算
End Sub

複製代碼

作者: ppppssss 時間: 2019-5-29 22:11

回復 ppppssss
GBKEE 發表於 2019-5-29 13:54

謝謝GBKEE大大的幫忙最近公司事情比較多我有時間再試試告訴你結果。

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)