%e3%80%90qa%e3%80%91%e9%80%a3%e7%b6%9a%e3%81%97%e3%81%9f%e3%82%bb%e3%83%ab%e3%83%87%e3%83%bc%e3%82%bf%e3%82%92%ef%bc%91%e8%a1%8c%e3%81%a5%e3%81%a4%e9%9b%a2%e3%81%97%e3%81%a6%e8%b2%bc%e3%82%8a

当サイトのコンタクトフォームへVBAのループについてお困りとのご質問が寄せられたので、せっかくなので直接返信ではなく、記事のかたちで回答したいと思います(・∀・)

質問者の方もご了承いただければ幸いですm(_ _)m


コンタクトフォームへ寄せられたご質問

タイトル

連続したセルデータを1行づつ離して貼り付け

本文

お世話様になります VBA初心者です。
標記のようにしたく
Sub 行飛び貼り付け()
Dim i
Dim h
For i = 200 To 210
Range(Cells(i, 2), Cell(i,30)).Copy
For h = 300 To 320 Step 2
Cells(h, 2).PasteSpecial Paste:=xlPasteValues
Next
Next
End Sub
としましたが連続したセルデータを読み込んでくれません ※同じデータを1行づつ離して貼り付けてはくれます。どのようにしたらうまく行くのかご教示頂けないでしょうか、よろしくお願いします

回答

まず、質問者の方が記述したVBAの検証から開始しました。

サンプルデータ

頂いたVBAのコードを見る限り、200~210行目のB~AD列を300行目以降に1行飛ばしでコピペしていきたいのだと察しています。

そこで、取り急ぎコピー元のデータとしてA~AD列の1~210行目まで自セルの番地を各セルの値にして用意してみました。

1%e8%a1%8c%e9%a3%9b%e3%81%b0%e3%81%97%e8%b2%bc%e3%82%8a%e4%bb%98%e3%81%91%e2%91%a0

コード検証

ここで質問者の方のVBAコードを起動してみます。

ちなみに、頂いたコードの5行目の2つ目のCellsプロパティの”s”が記述漏れがありましたので追記してます。
また、見やすいようにインデントも行ったものが以下のコードですね(*^-^*)

結果は以下の通りです。

1%e8%a1%8c%e9%a3%9b%e3%81%b0%e3%81%97%e8%b2%bc%e3%82%8a%e4%bb%98%e3%81%91%e2%91%a2

ご覧の通り、300・302・304・306・308・310・312・314・316・318・320行目のすべてに210行目のデータが入っていますね。

これはなぜかというと、上記の行すべてに、変数”i”で格納している200~210行目が順番にコピペするようなコードになってしまっているからです。

たとえば、一番最初の変数”i”が”200″の場合、300行目にコピペしただけで終わらず、302~320行目に1行飛ばしで200行目の値がコピペされてしまっているということですね。

なので、変数”i”の数だけ300~320行目の同じ箇所に上書きでコピペを繰り返しており、変数”i”の最後の値となる”210″が最後にコピペを行ったものになるため、マクロの終了結果が上図のようにすべて210行目のデータになっているわけです。

コード改善案

先ほどの結果からFor Nextステートメントのネスト(入れ子)は止めて、B列の最終行に応じてIfステートメントで条件分岐させる方向で改修してみました。

内容は以下の通りです。

コードの高速化のため「Application.ScreenUpdating」を5・26行目に、そして、コピーモードを解除するために「Application.CutCopyMode = False」を24行目に挿入していますが、ご質問内容に直接関係ないので、いったん無視してください。

では、本題のところの解説に移っていきます。

変数・定数

まず、2行目の変数の追加をしているのが、“LastRow”です。こちらは、「最終行」を特定するための変数です。
なお、変数”i”と”LastRow”は桁数の多い整数に対応するように変数のデータ型は「Long」を指定しています。こちらは特に指定しなくてもOKです。

次に、3行目は変数”h”であったものを定数に変更しています。
また、貼り付け先の開始位置となる行は300行目で固定するために定数にしました。
定数が分かりにくければ、変数で”300″を別途指定しても、結果は一緒です。

For Nextステートメントのカウンタ変数

7行目からがFor Nextステートメントによるループが開始となります。
こちらは貼り付け元の行数となる200~210行目を指定するのは変更していませんね。

変数”LastRow”

次に、10行目に変数”LastRow”にB列の最終行を格納します。
大事なポイントとしては、必ずこの最終行の取得はFor Nextステートメントの中に入れてください。理由としては、変数”i”の値が変わるごとに都度最終行を取得させたいからです。

なお、最終行の取得については、下記記事を元に詳細を確認してくださいね(*^-^*)

【Excel VBA】ワークシート内のデータがある最終行番号を取得する方法 | Excelを制する者は人生を制す ~No Excel No Life~

もし、B列の320行目より下の行に何かしらの値がある場合はうまく条件分岐しなくなるため、その場合は「Rows.Count」の部分を問題ない行数を指定するなどして、初回のループ時に210行目を取得できるようにしてくださいね。

Ifステートメント

続いて、13行目のIfステートメントで最終行の値によって処理を分岐させます。
分岐の条件は変数”LastRow”の値が”210″とイコールか否かです。

真の場合

イコールの場合の処理が14・15行目に該当します。
「イコールの場合」は変数”i”の最初の値となる”200″のみです。
つまり、ループの初回のみi行のB~AD列をコピーし、B300セルへ値貼り付けを行うわけですね。

偽の場合

そして、「イコールでない場合」の処理が18・19行目に該当します。

上記のループの初回の貼り付けが行われたことで、2回目のループの時点で変数”LastRow”に格納されるB列の最終行が”300″になりますね。
そうすると、コピー自体の処理は「イコールの場合」と同じくi行のB~AD列をコピーしますが、貼り付け場所を変数”LastRow”の2行下に指定する部分が異なりますね。
2回目のループであれば、変数”LastRow”が”300″なので、2行下のB列のB302セルへ値貼り付けを行います。

3回目以降は、B304セル以降へ1行飛ばしで貼り付け先のセルが下方向へずれていきますよ。

実行結果

よって、このマクロの実行結果は以下の通りとなります。

  • 200行目→300行目
  • 201行目→302行目
  • 202行目→304行目
  • 203行目→306行目
  • 204行目→308行目
  • 205行目→310行目
  • 206行目→312行目
  • 207行目→314行目
  • 208行目→316行目
  • 209行目→318行目
  • 210行目→320行目

実際のワークシート上では以下のようなイメージですね。

1%e8%a1%8c%e9%a3%9b%e3%81%b0%e3%81%97%e8%b2%bc%e3%82%8a%e4%bb%98%e3%81%91%e2%91%a4

関連記事

同じく1行飛ばしのループの方法について触れている記事です。
同じ1行飛ばしでも、入れるデータと入れたい場所によって、微妙にコードは変わりますね。

こちらも参考までにご参照ください。

【Excel VBA】1行飛ばしで転記するループ処理(For Nextステートメント) | Excelを制する者は人生を制す ~No Excel No Life~

まとめ

VBAに慣れていない間は頭の中のイメージはできていても、その通りに動いてくれないのは、私も身をもって知っています(;´Д`)

特にループは想定通りに動くようにするためには、小さく検証していくことが大事なので、「ステップイン」などのデバッグ機能やイミディエイトウィンドウを活用すると、混乱が少なくなりますよ。

ご参考になれば幸いですm(__)m

サンプルファイル

今回使用した改善後のマクロが入っているサンプルファイルを以下に置いておきます。

【サンプル】1行飛ばしで貼り付け.xlsm

ダウンロードの上ご自由にお使いください。
なお、こちらのファイルで何かしら問題が生じても当方で責任は負いかねますので、その点はご承知おきくださいませm(__)m

この記事が気に入ったら
いいね ! しよう