vbaの速度向上（sumif関数）

質問

エクセルvbaの速度を向上できないか、お知恵を貸していただきたく存じます。
以下のvba（sumif関数）をもっと速めたいです。何とかできないでしょうか。長い記載となり申し訳ないのですが、何卒よろしくお願い申し上げます。

myCnt7 = 2
Do
Worksheets("●").Cells(myCnt7, 4).Value = WorksheetFunction.SumIf(Worksheets("◆").Range("B:R"), Worksheets("●").Cells(myCnt7, 3), Worksheets("◆").Range("R:R")) - WorksheetFunction.SumIf(Worksheets("★").Range("B:C"), Worksheets("●").Cells(myCnt7, 3), Worksheets("★").Range("C:C"))
Worksheets("●").Cells(myCnt7, 7).Value = WorksheetFunction.SumIf(Worksheets("◆").Range("B:R"), Worksheets("●").Cells(myCnt7, 6), Worksheets("◆").Range("R:R")) - WorksheetFunction.SumIf(Worksheets("★").Range("B:C"), Worksheets("●").Cells(myCnt7, 6), Worksheets("★").Range("C:C"))
Worksheets("●").Cells(myCnt7, 10).Value = WorksheetFunction.SumIf(Worksheets("◆").Range("B:R"), Worksheets("●").Cells(myCnt7, 9), Worksheets("◆").Range("R:R")) - WorksheetFunction.SumIf(Worksheets("★").Range("B:C"), Worksheets("●").Cells(myCnt7, 9), Worksheets("★").Range("C:C"))
Loop While myCnt7 > 201

※シート●のＣ列から３列ごとに、Sumifの検索条件があります。
※シート●のＤ列から４列ごとに、Sumifの計算結果を出力させます。
※計算対象シートは、シート◆とシート★の２つです。
　シート◆のSumif合計から、シート★のSumif合計を差し引いています。
　Sumifの条件自体は、どちらのシートも同じ（シート●）。
※上記のSumif関数の記述は、３つですが、実際の記述は２４あります。
※すなわち、検索条件の組み合わせが２４あり、201行分をmyCnt7でLoopさせて実行しています。

回答No.1

dogs_cats · ベストアンサー

処理速度を上げるには画面更新停止する事が効果的です。
効果があるか不明ですが、データのある最終行を検索しセル範囲を設定し変数に格納後sumif関数に指定しています。

記載コードではmyCnt7をloop前で１を足していないのでmyCnt7は2のまま変動しません、よって無限ループで終了しないはずです。本当に終了して結果がでたのでしょうか？

確認はしていませんので、デバックして修正下さい。

Sub test()

Dim i, j As Integer
Dim M, MaxR1, MaxR2 As Long
Dim MyBR, MyRR, MyBC, MyCC As Range
Dim ws1, ws2 As Worksheets

Set ws1 = Worksheets("◆")
Set ws1 = Worksheets("★")

'画面更新停止
Application.ScreenUpdating = False

'Worksheets("◆")("★")B列の最終行取得
MaxR1 = ws1.Cells(Rows.Count, 2).End(xlUp).Row
MaxR2 = ws2.Cells(Rows.Count, 2).End(xlUp).Row

'sumifのセル範囲を設定
Set MyBR = ws1.Range("B1:R" & MaxR1)
Set MyRR = ws1.Range("R1:R" & MaxR1)

Set MyBC = ws2.Range("B1:C" & MaxR2)
Set MyCC = ws2.Range("C1:C" & MaxR2)

myCnt7 = 2

With Sheets("●")

Do

For j = 4 To 10 Step 3
.Cells(myCnt7, j).Value = WorksheetFunction.SumIf(MyBR, .Cells(myCnt7, j - 1), MyRR) - WorksheetFunction.SumIf(MyBC, .Cells(myCnt7, j - 1), MyCC)
Next

myCnt7 = myCnt7 + 1

Loop While myCnt7 > 201

End With

'画面更新再開
Application.ScreenUpdating = True

Set ws1 = Nothing
Set ws2 = Nothing
Set MyBR = Nothing
Set MyRR = Nothing
Set MyBC = Nothing
Set MyCC = Nothing

End Sub

お礼

ありがとうございました！
おかげで速くなりました。

回答No.2

kagakusuki

御質問文のVBAには幾つものおかしな点があります。

myCnt7 = 2
Do
(中略)
Loop While myCnt7 > 201

となっていますが、Whileとは条件が満たされていない場合に繰り返し処理を終了するというものであり、最初に　myCnt7 = 2　としているのですから、最初から　myCnt7 > 201　という条件は満たされていない訳で、それではDoとLoopの間に記述されている処理はたった１回しか行われませんので、繰り返し処理になりません。
　もし、Whileとしたのが間違いで、条件が満たされた場合に繰り返し処理を終了するという様にしたかったのだとしましても、DoとLoopの間にはmyCnt7の値を変更する様な処理は1つも記述されておりませんから、いつまで経ってもmyCnt7は2のままであり、Do～Loopの繰り返し処理が終了する事はありません。

　又、

>201行分をmyCnt7でLoopさせて実行しています。

との事ですが、前述の通り、myCnt7は2以外の値になる事はありませんから、処理が行われるのは2行目のみの１行分しかありません。
　又、Sumifの計算結果を出力させるセルを何行ごとに設けるのかも不明です。

　又、

>※シート●のＤ列から４列ごとに、Sumifの計算結果を出力させます。

との事ですが、Sumifの計算結果を出力しているのは
Worksheets("●").Cells(myCnt7, 4)
Worksheets("●").Cells(myCnt7, 7).
Worksheets("●").Cells(myCnt7, 9).
の３列ごとになっています。

　又、

WorksheetFunction.SumIf(Worksheets("◆").Range("B:R"), Worksheets("●").Cells(myCnt7, 3), Worksheets("◆").Range("R:R"))

ですと、Sheet◆のB列だけではなく、Sheet◆のB列～R列の中でSheet●のC3セルと同じ値を持つセルに対応する位置にある、Sheet◆のR列～AH列(←こちらもSheet◆のR列だけではない)のセルに入力されている数値の合計を求める関数になっていますが、本当にその様な合計の仕方をしたいのでしょうか？
　もしかすると

WorksheetFunction.SumIf(Worksheets("◆").Range("B:B"), Worksheets("●").Cells(myCnt7, 3), Worksheets("◆").Range("R:R"))

の間違いではないでしょうか？
　同じく

WorksheetFunction.SumIf(Worksheets("★").Range("B:C"), Worksheets("●").Cells(myCnt7, 3), Worksheets("★").Range("C:C"))

等も

WorksheetFunction.SumIf(Worksheets("★").Range("B:B"), Worksheets("●").Cells(myCnt7, 3), Worksheets("★").Range("C:C"))

の間違いではないでしょうか？

　上記の様に、おかしな点が幾つもあるため、質問者様が何をやりたいのかという事が良く解りません。
　そのため、取り敢えず叩き台として、以下の様な処理を行うVBAを提示しておきますので、もし質問者様のやりたい事とは異なっている点がある場合には、どこがどの様に違うのかを具体的に御説明願います。

※Sumifの計算結果を出力するセルは、シート●のC2:C201、G2:G201、及びJ2:J201の３つのセル範囲に含まれている全てのセル

※Sumifの検索条件は各「『Sumifの計算結果を出力するセル』の左隣のセルに入力されている値と同じ値」である事

※出力する値は、「『シート◆のB列の中で上記の検索条件を満たしているセル』と同じ行にある『シート◆のR列のセル』の値の合計」から「『シート★のB列の中で上記の検索条件を満たしているセル』と同じ行にある『シート★のC列のセル』の値の合計」を差し引いた値

　上記の様な処理を行うVBAの一例は以下の様なものとなります。

Sub QNo9088537_vbaの速度向上_案1()

Dim i As Long

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

With Sheets("●").Range("D2:D201")
For i = 0 To 6 Step 3
.Offset(, i).FormulaR1C1 = _
"=SUMIF(◆!C2:C18, RC[-1], ◆!C18) - SUMIF(★!C2:C3, RC[-1], ★!C3)"
Next i
Calculate
For i = 0 To 6 Step 3
.Offset(, i).Value = .Offset(, i).Value
Next i
End With

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With

End Sub

或いは以下の様なやり方もあります。

Sub QNo9088537_vbaの速度向上_案2()

Dim i As Long

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

With Sheets("●").Range("D2:D201,G2:G201,J2:J201")
.FormulaR1C1 = _
"=SUMIF(◆!C2:C18, RC[-1], ◆!C18) - SUMIF(★!C2:C3, RC[-1], ★!C3)"
Calculate
.Value = .Offset(, i).Value
End With

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With

End Sub

お礼

ありがとうございます。いろいろとご指摘いただき、勉強になりました。