[發問] 誰能幫這幾乎重複動作的VBA瘦身 謝謝

lcctno

由於在下是vba初學者 故嘗試將我使用函數寫的數百mb之excell檔 使用錄製巨集的方式 來將該檔瘦身 雖然瘦身後可正常使用 但我想應該程式碼也有簡化的可能性
我知道可以將  Rows("3:101").Select 改成 Rows("3:1001").Select 但會發生類似系統資源不夠的警告語 故只好寫成一次只判斷100筆的資料 但我目前的資料也接近6000筆了 且還會繼續增加
希望有高手能幫助我簡化它 並讓它能順利的執行到 10001筆 (為將來留後路) 謝謝了

目前瘦身的處理過程是將每一分頁 指保留一列之函數 使用vba將之複製並貼上99列函數後 在將附製上之結果變成純文字再貼回原處 依此類推...工作表.由"析1" 到"析10"  總共10分頁 如附圖

下載 (210.62 KB)

2015-8-23 18:31

分隔線++++++++++++++++++++++++++++++++++++++++++++++
'判斷列1之值是否在工作表"Data"對應之每日高低值的範圍內

Sub 判斷列()

'2~101

'製作判斷列
    Sheets("析1").Select
    Rows("2:2").Select
    Selection.Copy
    Rows("3:101").Select
    ActiveSheet.Paste
   
'瘦身
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
'製作判斷列
    Sheets("析2").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("3:101").Select
    ActiveSheet.Paste
        
'瘦身
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
  ...............
. ....略過很多比工程碼...............


'製作判斷列
Sheets("析10").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("202:301").Select
    ActiveSheet.Paste
            
'瘦身
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Sheets("Data").Select
End Sub


匯出之巨集
判斷列.bas.zip (640 Bytes)

下載次數: 18

2015-8-23 18:48

准提部林

回復 67# lcctno


但是有一個問題 儲存離開後 再開啟 發現若改變統計日期之區間 就不會改變數據 且打開時並沒有明顯的運算動做
這樣那檔案就是只能一次性使用(除非是不想改變統計區間 或輸入其他股票的data 有沒有辦法加一個開關 隨時可隨性而用
想來源資料變動，公式自動變動，那就是〔常駐公式〕，也是因這個而讓操作檔案礙手，
所以，資料變動，只能提供〔以VBA刷新〕方式得到最新結果，，因此，兩種VBA只是用來驗證兩程結果是否一致？
若資料不多，就保持公式常態，連VBA都可省去了！
若資料很多又要保持公式，恕無方法讓檔案不卡（關掉自動重算，與使用VBA同樣效果，但會造成其它公式無法運算，不是好方法），

.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=資料列_最高價)*(A3>=資料列_最低價)))"  
這不是附檔中的公式嗎？只是利用VBA去置入！也可用〔錄製〕取得FormulaR1C1的公式！

有些問題需要累積經驗才懂，一時無法說太詳細，請慢慢參酌～～
　

lcctno

回復 66# 准提部林
報告高手
執行中須等待些時間（有如當機）
執行這項用不到4秒 很值得
但是有一個問題 儲存離開後 再開啟 發現若改變統計日期之區間 就不會改變數據 且打開時並沒有明顯的運算動做 這樣那檔案就是只能一次性使用(除非是不想改變統計區間 或輸入其他股票的data 有沒有辦法加一個開關 隨時可隨性而用
很報歉較慢回覆 因在做測試那執行檔 (由於看不懂 只能實做看現象) 所以花了不少時間

.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=資料列_最高價)*(A3>=資料列_最低價)))"  
請麻煩告訴我有這行是什麼用途?

謝謝您的幫忙

准提部林

回復 64# lcctno


本來穿褲子（公式）嫌慢，改成裙子（vba）較快些，
現在想褲子再穿搭裙子，問題又回到原點，
公式範圍第一格留公式，其下複製後再貼成值（以５５樓範例檔為準）：
Sub 更新公式()
With [結果1!B4:B2562]
　　　[結果1!B3].Copy .Cells
　　　.Value = .Value
End With
End Sub

這樣只有第一格公式會影響運作～～

或如下：
Sub 更新公式2()
With [結果1!B3:B2562]
　　.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=資料列_最高價)*(A3>=資料列_最低價)))"
　　.Value = .Value
End With
End Sub

執行中須等待些時間（有如當機），完成後即不會影響其它操作！
　

准提部林

回復 62# ML089


哈! 還是新版有料~~
謝謝！

之前都用奇摩知識+，現在版面一改已面目皆非，幾無excel專用效果！
excel-home非常專業，但台灣少人用，有時串串門子，
台灣比較專于excel論壇的，似乎只剩這裡，而各位板主功力也幾可幫忙大部份問題，所以也少上來（上來也是看看而已）！

lcctno

回復 47# 准提部林

請問有沒有辦法讓我這檔案 (除了拆單外)
能夠於運算完後 就將某個功能關掉 好讓它把資源讓出來
也就是說在執行同樣那檔 但不需要它在即時運算
因為統計完後就照表操課了 只是還會需要用到別的部分
有勞您了 謝謝
我現在是"函數+vba" double check 也比較放心

ML089

回復 61# 准提部林

＿＿登錄時都勾選了〔自動登錄〕，每次進來論壇還要再輸入帳號密碼，真不方便！

我的會自動登錄(WIN10+CHROME)
但一段時間(好像一個星期吧)就會要求重新登錄，若你不常上來就會感覺每次登錄都要重新輸入帳號密碼。

ML089

回復 61# 准提部林


MMULT 在2007版
1. 測試 1000000X2 及 2X1000000 都可以跑
2. 測試 4000X4000以上就會出現公式資源不足的訊息

准提部林

回復 60# ML089


全域陣列確實較快省資源!

MMULT在XP+OFFICE2000下, 最多可用到5643列, 再多就#VALUE,
不知其他版本可用到多少列?


＿＿登錄時都勾選了〔自動登錄〕，每次進來論壇還要再輸入帳號密碼，真不方便！
　

ML089

回復 53# lcctno


原來的也是OK，修改一些公式感覺快一些。

大盤2011-12-19起 (ML089).rar (120.57 KB)

下載次數: 9

2015-8-30 18:08