エクセル マクロ マクロの作業の分割

こんばんは、エクセルのマクロで解らない事があるので教えて頂けませんか？

以下のようなコードでデータを複製しています。

自分が思うにはそんなにPCに負担をかけているつもりはないのですが、何故かリソース不足になってしまい、最終的にはメモリ不足の警告が出てしまい、作業が中断されてしまいます。

原因も知りたいと思うのですが、作業も進めたいので分割してやる方法があれば知りたいと思います。

求める結果は　AKR１~ALX８データを下段に８行ずつ数式ごと複製して行くと言うものです。

ちなみに総データ数は２２万行です。
８行１ステップなので、27000ステップ程度。
数式は他セル参照とVLOOKUP程度です。

少量データでのテストは、1000ステップが9秒程度、10000ステップが170秒。
27000ステップありますので600秒もあれば終わるはずなのですが、実際はリソース不足になり以下の部分でエラーが出てしまいます。

Range("AKR" & i & ":ALX" & i + 8).PasteSpecial Paste:=xlPasteFormulas

************
コード

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Long
Dim St As Long
Dim En As Long

St = 1
En = InputBox("繰り返しの回数は", 100) * 8
For i = St To En Step 8

Range("AKR1:ALX8").Copy
Range("AKR" & i & ":ALX" & i + 8).PasteSpecial Paste:=xlPasteFormulas

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
************

詳しい方、よろしくお願いいたします。

No.7 ベストアンサー 回答者： enunokokoro 回答日時： 2015/12/30 17:06

No.4の回答者です。

エラーの原因を考えてみては？

私が示したマクロは、クリップボード経由よりも軽いと思うので、処理も
早く済むためにエラーが早く出たのだと思います。

リソース不足のエラーが出る原因が、マクロ処理のためかもしれません。
そうだとしても、リソース不足となるメモリ不足が何に影響されているか
検証することも、今後のためだと思います。
http://www.bmoo.net/archives/2015/09/315586.html
http://officetanaka.net/excel/vba/error/executio …

マクロの実行範囲を狭くして、どの行まで貼り付けるとエラーになるのか
確認することで、マクロが影響しているのか、数式を貼り付けた範囲での
何らかの処理ができないためなのかを確認できると思います。
次のマクロは、No.3のマクロを空白行から貼り付けします。
*******************************************

Sub test_2()

Dim i As Long
Dim St As Long
Dim En As Long

' 空白セル直前の最終行を取得
St = Range("AKR1").End(xlDown).Row
' 最終行の次の行を指定
St = St + 1

' 繰り返し回数　0回の場合は8行追加するだけ
En = InputBox("繰り返しの回数は", 100) * 8
For i = St To ST + En Step 8

' 指定行の内容を、数式として空白行以降に置き換え
Range("AKR" & i & ":ALX" & i + 7).FormulaR1C1 = Range("AKR1:ALX8").FormulaR1C1

Next i

End Sub

************************************************
繰り返し回数を100回程度にしてマクロを実行したときに、追加した行が
どの程度の実行をした回数でエラーになるのかで、マクロでメモリ不足に
なっているのか、それともシート内の数式などの量に影響しているのか、
確認できると思います。

質問のマクロでも他の回答のマクロでも同じですが、指定範囲をコピーで
取得しても、貼り付ける位置が1行目からでは無意味ですよね。
100回貼り付けをしても、1行目から800行目までの貼り付けになります。
そのままマクロを再度実行で100回を指定しても、1行目から800行目まで
貼り付けることになるので、前回貼り付けた意味がなくなります。
質問にあるマクロでも、開始行と貼り付けの繰り返し回数を、空白行から
始まるようにすれば、余計な処理がなくなります。

************
' コード　貼り付け位置を空白行から指定

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Long
Dim St As Long
Dim En As Long

' この部分を追加
St = Range("AKR1").End(xlDown).Row
St = St + 1

En = InputBox("繰り返しの回数は", 100) * 8
' 空白行から、繰り返しの8倍＋空白行の行番号で貼り付け
For i = St To St+En Step 8

Range("AKR1:ALX8").Copy
Range("AKR" & i & ":ALX" & i + 8).PasteSpecial Paste:=xlPasteFormulas

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
************

貼り付けを行う範囲が空白行でないと、このマクロは意味を成しません。
その場合は、貼り付ける範囲をクリアにしておく必要があります。

この回答へのお礼

回答いただきありがとうございます。

お陰様でなんとかできました。
マクロにはいろいろやり方や注意点があって面白いですね。

ありがとうございまいした！

お礼日時：2016/01/10 06:23

No.9 回答者： hallo-2007 回答日時： 2015/12/30 17:48

何度もすみません。

No8、６のやり方はちょっと無理がありました。

Range("AKR1:ALX8").Copy
Range("AKR" & St & ":ALX" & En).Select
ActiveSheet.Paste
の3行は必要かも？

この回答へのお礼

何度も教えていただき、ありがとうございます！

お陰様で無事できました。
大変感謝いたします。

ありがとうございました！

お礼日時：2016/01/10 06:21

No.8 回答者： hallo-2007 回答日時： 2015/12/30 17:25

No3、NO6ですが

＞今回教えて頂いた、Value　Fomulaですが両方とも値で貼り付けるものなのでしょうか？
Fomula　は数式です。
式をコピーする違いで　.FormulaR1C1　もあります。
たぶん、今回のご希望は　.FormulaR1C1？

St = 1
En = InputBox("繰り返しの回数は", 100) * 8
Range("AKR" & St & ":ALX" & En).FormulaR1C1=Range("AKR1:ALX8").FormulaR1C1
と一行でOKかも？

No.6 回答者： hallo-2007 回答日時： 2015/12/30 10:15

いっそのこと、繰り返しを使わず

St = 1
En = InputBox("繰り返しの回数は", 100) * 8
Range("AKR1:ALX8").Copy
Range("AKR" & St & ":ALX" & En).PasteSpecial Paste:=xlPasteFormulas
では、どうでしょうか？

No.5 回答者： NURU_osan 回答日時： 2015/12/30 08:20

№1と2で回答したものですが再回答します。

もし、数式だけでなく書式等もまるごとコピーしてよいのであれば・・・

Range("AKR1:ALX8").Copy
Range("AKR" & i & ":ALX" & i + 8).PasteSpecial Paste:=xlPasteFormulas

を

Range("AKR1:ALX8").Copy Range("AKR" & i & ":ALX" & i + 8)

の1行にまとめると処理が少し軽くなるはずです

あとNextの前に

DoEvents

という命令文を1行追加してみてください。
これで処理速度は若干遅くなりますが、遅くなった分だけPCにかかる負荷が軽減されます。

あとはどうもコピーする範囲が固定のようなので

For i = St To En Step 8

Range("AKR1:ALX8").Copy
Range("AKR" & i & ":ALX" & i + 8).PasteSpecial Paste:=xlPasteFormulas

Next

となっている部分を丸ごと

Range("AKR1:ALX8").Copy Range("AKR9", ":ALX" & En+ 8)

の1行にまとめて一発でコピーするとかしちゃダメなんでしょうか？

この回答へのお礼

回答ありがとうございます。
何度も考えて頂きありがとうございました！

お礼日時：2016/01/10 06:22

No.4 回答者： enunokokoro 回答日時： 2015/12/29 23:52

FormulaR1C1を使ってみては。

http://miyahorinn.fc2web.com/vb/vb002.html
No.3さんの数式と同じように、右辺の範囲を左辺の範囲に数式などで適用
するのに適しているからです。

Value　セルの値で
Fomula　セルの参照式をそのまま
FormulaR1C1　セルの相対参照式

PasteSpecial Paste:=xlPasteValues　→　Value
のように値で貼り付けるのと同じようになります。
PasteSpecial Paste:=xlPasteFormulas　→　FormulaR1C1
のように、数式を貼り付けたのと同じようになります。

Sub test()

Dim i As Long
Dim St As Long
Dim En As Long

St = 1
En = InputBox("繰り返しの回数は", 100) * 8
For i = St To En Step 8

Range("AKR" & i & ":ALX" & i + 7).FormulaR1C1 = Range("AKR1:ALX8").FormulaR1C1

Next i

End Sub

この回答へのお礼

回答ありがとうございます。

一応試してみたのですが、今までのものよりエラーが出るのが速かったです。

R1C1は表示する方法論だと言う認識でしたが、これにより計算の過程が何か違うと言うものなんでしょうか？
また、メモリへの負担と言うのはどうなんでしょうね？

回答ありがとうございました。

お礼日時：2015/12/30 03:03

No.3 回答者： hallo-2007 回答日時： 2015/12/29 15:40

コピーとペーストの繰り返しですのでPCに負担を与えています。

Range("AKR1:ALX8").Copyが毎回必要なのでしょうか？
For文の前に一度だけにして試してみる。

書式などまで、コピーする必要がありますでしょうか
Range("AKR1:ALX8").Copy
Range("AKR" & i & ":ALX" & i + 8).・・・
を
Range("AKR" & i & ":ALX" & i + 8).Value=Range("AKR1:ALX8").Value
或いは
Range("AKR" & i & ":ALX" & i + 8).Formula=Range("AKR1:ALX8").Formula
と一行にまとめて、値だけの入れ替えては如何でしょうか。

この回答へのお礼

前回は回答をいただきありがとうございます。

今回は少しデータが増えて、同じ作業をやっていたのですが、このような結果になってしまいました。

それで内容ですが、コピーする内容にVLOOKUPが入っていて、オートフィルが使えないので8行まとめてコピー＆ペーストと言う形を取りました。

VLOOKUPの参照範囲が8行単位なんです。

今回教えて頂いた、Value　Fomulaですが両方とも値で貼り付けるものなのでしょうか？

数式も欲しいのですが。

お礼日時：2015/12/29 20:42

No.2 回答者： NURU_osan 回答日時： 2015/12/29 11:01

ではコードの最後で変数を初期化してみてください。

i = 0
St = 0
En = 0

End Sub


Excelを再起動の上、やり直してできなければ、コードの全貌が明らかではないのでこれ以上はわかりません。

この回答へのお礼

再回答ありがとうございます。

やってみたのですが、やはり途中で不足しますね。

コードは記載したものがすべてなのですが、、、悩みます＞＜

お礼日時：2015/12/29 20:44

No.1 回答者： NURU_osan 回答日時： 2015/12/29 09:14

あぁ、コピー＆ペーストを使いすぎてクリップボードがいっぱいになって「リソース不足」に陥っています。

コピー＆ペーストの後でクリップボードをクリアするコードを追加する必要があります。
下記の末尾に「'」がついた２行を追加してみてください。

Range("AKR" & i & ":ALX" & i + 8).PasteSpecial Paste:=xlPasteFormulas

Range("a1").Copy Range("a1") '
Application.CutCopyMode = False '

Next

この回答へのお礼

回答ありがとうございます。

なるほどです、そんな事になるんですね勉強になりました。

それでやってみたのですが、結果は同じ＞＜

なんででしょうね・・・Orz

お礼日時：2015/12/29 10:29