「プロシージャが大きすぎます」・・・？

質問

こんにちは。最近エクセルのマクロを使っています。
使っているといっても全く初心者で、自分で作るというよりは、マクロを記録させて、ちょこっと編集したりして、なんとか便利に使っています。

ところで、最近、まったく同じレイアウトの400枚分のシートに置換を施さなければ行けない事態になったので、上の方法で一枚分だけマクロをつくって、あとはコピーペーストで400枚分に増やし、シート名とか必要な部分をちょこっと編集して、実行しました。

一度目はほとんどうまく実行できたのですが、全てのシートのセルを結合した部分だけ置換されなかったので、こんどは上のマクロに、「Range("B2:D6").Select」をつけたし、選択部分だけ置換するように書き換えて別のマクロとして保存しました。

ところが、このマクロを実行しようとすると、「コンパイルエラー：プロシージャが大きすぎます」というエラーが出てしまい、実行できません。

いったいこのエラーは何なのでしょうか？そしてどのように解決したら良いのでしょうか？

回答No.6

papayuka · ベストアンサー

他の方への補足をみるとこんな感じでしょうか？

Sub Test2()
Dim i As Integer
　For i = 1 To 400
　　　Worksheets("Sheet" & i).Range("B5:D9").Replace _
　　　　What:="$401", Replacement:="$" & i + 1, LookAt:=xlPart, _
　　　　SearchOrder:=xlByRows, MatchCase:=False ', _
　　　　'SearchFormat:=False, ReplaceFormat:=False
　Next
End Sub

実行すると、
シート名 Sheet1 の B5:D9 にある $401 を $2 に
シート名 Sheet2 の B5:D9 にある $401 を $3 に
　　　　　・
　　　　　・
シート名 Sheet400 の B5:D9 にある $401 を $401 に？
って感じになると思います。

お礼

papayukaさん！でっ！！できましたよ！
途中、うっかりマクロをどこかにやってしまって（多分、頭の緑色にの文字を消してしまったから？）しばらく四苦八苦してしまい、お礼が遅くなってすみません。
お忙しいのに、丁寧な回答をありがとうございました。このマクロでばっちりできました！ど素人の私にもわかりやすい説明で、ほんとうに勉強になりました。大感謝です（^o^/
ほんとうにありがとうございました。

回答No.5

papayuka

またまた、こんにちは。

明確にどの Sheet の Range を操作するか指定してやらないとダメです。
SearchFormat と ReplaceFormat は私の使っている Excel97 には無いので、例ではコメントアウトしています。

同一ブック内の全てのシートをループして、B5:D9に "$401" が ある場合は "$2" にします。

Sub Test()
　For i = 1 To Worksheets.Count
　　　Worksheets(i).Range("B5:D9").Replace _
　　　　What:="$401", Replacement:="$2", LookAt:=xlPart, _
　　　　SearchOrder:=xlByRows, MatchCase:=False
　　', _SearchFormat:=False, ReplaceFormat:=False
　Next
End Sub

回答No.4

papayuka

再びこんにちは。例えば、自動記録で、Macro1を作ったが長くて実行出来ない場合、
Sub Macro1()　'←長すぎるMacro1
　・
　・
　・
　・
End Sub

下記のようにちょっと手を加えると実行出来たりします。

Private Sub Macro1()　'←Private を Sub の前に加える
　・
　・
End Sub　'←区切りのよさそうな所に書き加える。
Private Sub 続き()　'←適当な名前で Private Sub を書き加える。
　・
　・
End Sub

Sub 実行()　'←適当な名前の Sub を作り、これを実行する。
　Macro1　　'←実行した順番にマクロ名を書く
　続き
End Sub

あと、同じ処理を複数のシートにする場合は、ループで処理するのが普通です。
ブラウザ上で書いたのでちゃんと動くか解からないけど、こんな感じで。

Sub Test()
　For i = 1 to WorkSheets.Count
　　'↓この間に処理したい事を書く
　　MsgBox WorkSheets(i).Name
　　'↑この間に処理したい事を書く
　Next
End Sub

お礼

papayukaさん、丁寧な解説、ありがとうございます。
超初心者の私でも大変わかりやすく、本当に感謝です。早速やってみます。

最後の「ループで処理」がちょっと分からないのですが、下の補足を参考にしてちょっと書いてみると、こんなかんじでしょうか？

Sub Test()
For i=1 to WorkSheets.Count
Range("B5:D9").Select
Selection.Replace What:="$401", Replacement:="$2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgNox WorkSheets(i).Name
Range("B5:D9").Select
Selection.Replace What:="$401", Replacement:="$2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
End Sub

なぜか、行頭のスペースがうまく出てきませんが、ご容赦ください。

回答No.3

wildcard

試しにB2:D6の結合セルを全て結合解除してから、マクロを実行してみては、いかがでしょう？これでエラーが出なくなるようでしたら、結合が原因であると言えます。
もし、結合が原因だとしても、結合解除以外に解決方法があるかも知れませんので、補足として、マクロのコードを記載してみてはどうでしょう？

補足

wildcardさん、再度回答ありがとうございます。
マクロは以下のとおりです。

Sheets("Sheet1").Select
Range("B5:D9").Select
Selection.Replace What:="$401", Replacement:="$2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

これが、
「Sheets("Sheet1").Select」の部分と
「Replacement:="$2"」の部分を変えて、延々と400シート分続いています。つまりSheet1からSheet400までと、$2から$401 までです。

マクロでなく、手作業で同様の置換作業をしてみると、結合セルの混在に関わらず、選択部分はきちんと置換されるのですが・・・。

回答No.2

papayuka

こんにちは。

単純に、プロシージャの行数が多すぎるのでは？
適当なところで複数に別けて、連続実行用のプロシージャを作ってみては？

お礼

papayukaさん、回答ありがとうございます。
実は私、ただの初心者ではなく「超初心者」で、しかもマニュアルなしで「なんとなく」な作業をしているもので、「プロシージャ」がなんなのかもわからないのです。SubからEnd Subまでの間にあるコマンド（？）のことでしょうか？
そして「連続実行用のプロシージャを作る」とはどんな作業をすれば良いのでしょうか？
お時間のある時で構いませんので、またお教え下さい。

回答No.1

wildcard

もしかして、これに当てはまるとか？
http://search.microsoft.com/gomsuri.asp?n=1&c=rp_Results&siteid=japan&target=http://www.microsoft.com/japan/support/kb/articles/JP213/5/79.asp

参考URL：

http://search.microsoft.com/gomsuri.asp?n=1&c=rp_Results&siteid=japan&target=http://www.microsoft.com/japan/support/kb/a

お礼

早速お返事いただき、ありがとうございます。
参考ページ、拝見しました。事例としては似ているのですが、選択したくない部分まで選択してしまうわけではないんです。それとも選択範囲内に、結合セルとそうでないセルがあるのがいけないのでしょうか？

時間があれば、またアドバイスお願いします。