エクセル VBAで関数

質問

こんばんわ。いつもお世話になっております。

エクセルでデータを加工しているのですが、関数で加工するととても遅くなってしまうので
なんとかVBAで作業できないかと模索中です。

Sheet1の、Ａ列＝取引先　Ｂ列＝支店名　となっており、それが3,000行ほどあります。
Sheet2も、Ａ列＝取引先　Ｂ列＝支店名　となっており、
Sheet1のＣ列に、Sheet1のＡ列のＢ列という支店が、Sheet2にあるか確認したいのです。

現在は、Sheet1のＣ列に、SUMPRODUCT関数でカウントさせているのですが、大変重いです。
ほかのSheetで、VBAを使って関数のような働きをさせている部分があります（前任者作成）
そのように、なんとかVBAを使用したいのですが、なかなかうまく出来ません・・・。

どうか、お力を貸してください！！
うまく説明できなくて、わかりづらかったらごめんなさい。
よろしくお願いいたします。

回答No.2

soaikats · ベストアンサー

ではこんな方法はどうでしょう。
高速化が見込めるかは未確認です。

１．Sheet1と2両方に空白行を挿入。
２．両方のシートのA1を「=CountA(A2:A65536)」とする。
３．Alt+F11でVBE画面を表示する。
４．挿入、標準モジュールをクリックする。
５．以下のFunctionプロシージャを貼付け。
６．Sheet1のC2を「=KOUSIN(A2,B2,Row())」

Function KOUSIN(sHonten as String, sShiten As String, iRow as Integer) as Integer
　Dim i As Integer
　Dim j As Integer

　j = Sheets("Sheet2").Range("A1").Value + 1
　i = iRow
　KOUSIN = Application.Evaluate("=sumproduct((Sheet2!A2:A" & j & "=""" & sHonten & """)*(Sheet2!B2:B" & j & "=""" & sShiten & """))")
End Function

お礼

アドバイスありがとうございます！！
お礼が遅くなってしまい、申し訳ございませんでした

とても勉強になります＾＾
このアドバイスを参考に、今後も頑張って勉強しますね！！
またぜひお知恵を拝借させてください＾＾

本当にありがとうございました。

回答No.1

soaikats

何かしらの更新がかかるたびに再計算がかかっていると
思われます。必要な時だけ更新処理を流すようにしては？

１．Sheet1と2両方に空白行を挿入。
２．両方のシートのA1を「=CountA(A2:A65536)」とする。
３．Sheet1に以下のSubプロシージャを貼付け。

Sub KOUSIN()
　Dim i As Integer
　Dim j As Integer

　j = Sheets("Sheet2").Range("A1").Value + 1
　For inti = 2 To Range("A1").Value
　　Range("C" & i).Value = Application.Evaluate("=sumproduct((Sheet2!A2:A" & j & "=""" & Range("A" & i).Value & """)*(Sheet2!B2:B" & j & "=""" & Range("B" & i).Value & """))")
　Next inti
End Sub

いかがでしょうか。

補足

アドバイスありがとうございます！
なるほど、再計算ですね～
参考になります！！
ですが、ごめんなさい。
知りたいのは、マクロの中で計算させて、結果のみ表示なんです＞＜。
せっかく時間を割いてくださったのに、本当にすみません
もしよかったら、結果のみ表示させる方法をご教授願えないでしょうか・・・。
もうしばらく、このまま回答をお待ちしております。
ありがとうございました！