excelマクロの処理を早くしたいのですが！良い知恵ある方お願いします。

質問

excelマクロはここ半年位やっています。
あるマクロ(VBA)を作ったのですが、表が大き過ぎて思うような処理スピードが出ません。ハードは富士通FMV6750CL7Sで750MのCPU速度です。ＯＳはWINDOWS2000を使っています。環境的には問題が無いと思っています。
何か良い知恵がある方いらっしゃれば宜しくお願い致します。

（内容）
sheet1・sheet2・sheet3にそれぞれ、3000行の表があります。
三つの表には、受注番号と言う名前の識別項目が、それぞれ1列だけ用意されています。表は受注番号順にソートしています。
マクロはsheet1のある行の受注番号を見て、それと同じ受注番号をsheet2から検索して、該当する行を求めます。次にsheet2の受注番号からsheet3の受注番号を検索して、該当する行を求めます。
（実際にはもうちょっと複雑ですが・・・）

遅い部分を調べて見ますと、3000行の表から該当する行を検索する部分に時間が掛かっている所までは分かっています。
先頭行から順じ見に行って該当する行が見つかったら検索終了するようにはしています。
また、データの存在しない行を見に言ってはいないようです。

（検索部分はこんな感じです）
For Each objA In Worksheets(Sheet2).[A:A]
　　If objA.Text Like 受注番号 Then
　　　intStr = objA.Row
　　End If
　　If objA.Text > 受注番号 OR objA.Text = ""Then
　　　Exit For
　　End If
Next

Accessなどのデータベースを使えと言われそうですが、excel内で処理すると言う絶対条件がありますので、苦慮しております。

このようなご経験のある方宜しくお願い致します。

回答No.4

BraveStory · ベストアンサー

受注番号順にソートしてあるのなら、「二分探索方」が使えます。
私も以前数千件からの検索のマクロを組んで処理があまりにも遅かったため、
いろいろ試してみて結局これが一番高速だったことがあります。

方法ですが、最初に検査値と全データの真ん中の値をくらべ、検査値が大きいときは、次に上半分の真ん中と比較し、検索値が小さいときは下半分の真ん中と比較します。これを繰り返していき、検索範囲を縮めていくと、3000件程度のデータなら10回程の比較でみつけることができます。

プログラム上追加で用意する変数は次の３つになります。
・比較対照の最小値を入れる変数、初期値は先頭行
・比較対照の最大値を入れる変数、初期値は最下行
・検索位置を格納する変数、初期値は真ん中の行

もし検索データが存在しないときの判断は、最小値変数が最大値変数より大きくなった場合で判断します。

説明が下手で分りにくいかもしれませんが、有名で一般的な検索方なので、どこかに説明のサイトがある気がします。

もしどうしても分らないようであれば、サンプルソースを作りますのでおっしゃってください。

お礼

有り難う御座います。
汎用性があって一番良いかと思っています。
むかしCOBOLの解説本にあったような記憶があります。

回答No.3

vbafriend

初めまして。私ならば、Findメソッドを利用すると思います。
ヘルプで確認して使用されてみてはいかがでしょうか。

お礼

有り難う御座います。
FINDメソッドは最初に検討してみましたが、難しいかと思って除外してました。

回答No.2

yuu200

Excel関数を使ってみてはいかかでしょうか。
VLOOKUP関数とか。
試してないので、もしかしたらもっと遅くなるかも…

お礼

有り難う御座います。
VLOOKUPはよく使います。
今回は難しいかと思っています。

回答No.1

noname#181872

この受注番号がどういう形式かわからないのですが、単なる数字の羅列（ハイフンとか
アルファベットが入っていない）とします。

そうしたら、15行なり20行なりずつ飛ばして検索したらいかがでしょうか？
で、検索をした受注番号が欲しい番号を超えていなければ次を検索、超えていたら
その番号までの15行あるいは20行を再検索するのです。そうしたら、
いままでのプログラムで検索する場合最大3000回のループを通していたところを、
最大でも34回のループですむと思うのですが。いかがでしょうか。

あとはマクロを動かしている途中、画面更新をさせないと多少速くなりますね。

お礼

ありがとう御座います。
コーディングが楽そうですね。
検討してみます。