Excel VBAのループの一つ、For Nextステートメントは使用頻度が高く、基本ともいえます。
【Excel VBA】For Nextステートメントで同じ処理を指定した回数分繰り返す方法 | Excelを制する者は人生を制す ～No Excel No Life～
今回はそんなFor Nextステートメントの応用テクニックとして、Excel VBAで1行飛ばしで転記するループ処理について解説していきます。
マクロでしたいこと
「印刷サンプル」というブックのD列の2行目以降を別ブックにマクロで転記します。
転記先は「VBAサンプル」というブックのA列の2行目以降ですが、このブックは1レコードにつき2行必要なため、転記する際は1行飛ばしの偶数行に転記が必要になります。
今回はこの条件でVBAを記述していきます。
コードサンプル
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub 一行飛ばしで転記() Dim i As Integer Dim LastRow As Long Dim WB As Workbook Set WB = Workbooks("印刷サンプル.xlsx") WB.Activate Sheets("ツール").Select LastRow = WB.Sheets("ツール").Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To LastRow ThisWorkbook.Sheets("Sheet1").Cells(i * 2, 1) = WB.Sheets("ツール").Cells(i + 1, 4) Next End Sub |
2行目にカウンタ変数として"i"をデータ型Integerを指定しています。（データ量が多ければLongにしましょう）
3行目はブック「印刷サンプル」の転記データの最終行を特定するための変数として"LastRow"をデータ型Longで指定しています。
4行目は頻出のブック「印刷サンプル」を簡略化するための変数"WB"をデータ型Workbookで指定しています。
5行目は変数"WB"にブック「印刷サンプル.xlsx」をセットします。
6行目は変数"WB"をActivateメソッドで選択します。
7行目はさらに「ツール」シートをSelectメソッドで選択します。
6・7行目は1行で記述しようとするとエラーになるので注意しましょう！
8行目は、変数"LastRow"に「印刷サンプル」の「ツール」シートの1列目（A列）のデータがある最終行の行数が入ります。（データ量にあわせて行数が自動で変わります）
こちらの部分は下記記事をご参照ください。
【Excel VBA】ワークシート内のデータがある最終行番号を取得する方法 | Excelを制する者は人生を制す ～No Excel No Life～
9行目はFor Nextステートメントの繰り返し範囲を指定します。
カウンタ変数"i"の範囲は今回「1」から変数"LastRow"までとなります。
（変数"LastRow"は行数の数値が入ります）
10行目は、For Nextステートメントで繰り返し処理を行う内容を記述します。
今回は「VBAサンプル」のシート「Sheet1」の1列目の2の倍数の行に、変数"WB"に格納した「印刷サンプル」のシート「ツール」の4列目のデータを転記になるので、イコール（＝）でつなげます。
このマクロが記述されているのが転記先の「VBAサンプル」になるため、ブック名は"ThisWorkbook"で省略できます。
シート部分は普通にSheetsプロパティで「Sheet1」を指定します。
セルが今回の肝で、Cellsプロパティでセル番地を行列の番号でカウンタ変数に応じて可変になるよう、行番号はカウンタ変数"i"に倍数となる2の乗算を指定、列番号はA列を示す「1」を指定します。
イコール（＝）を挟んだ右辺には参照元になる「印刷サンプル」の「ツール」シートのCellsプロパティでセル番地を指定します。
行番号は「印刷サンプル」は1行目が見出しでデータ部分は2行目からになるため、カウンタ変数"i"に「1」を加算し、列番号はD列を示す「4」を指定します。
あとは11行目にFor Nextステートメントの結びとなる「Next」を忘れずに入力しましょう。
マクロの動作イメージ
「VBAサンプル」のA列の偶数行に「印刷サンプル」のD列のデータがすべて転記されました！
ちなみに「VBAサンプル」のA列はセルが1行目を除き2行分を結合していますが、結合された状態でも問題なく転記されます。
まとめ
繰り返し処理は本当に事務処理の効率化に直結します！
私もまだまだ勉強中ですが、繰り返し処理のいろいろなパターンを使いこなせるように精進していきたいですね。
ご参考になれば幸いです(^^)
こちらのVBA構文でお聞きしたいことがございます。
今回、２行おきに貼り付ける内容となっておりましたが、これが、最初だけ４行、そこから3行おきに貼り付けるようにしたのですが、可能でしょうか。
まっつんさん
コメントありがとうございます。
ご質問の件、結論から言うと「可能」です。
ループの1回目が4行目、2回目以降は3行ずつ増えていきたいなら、本記事のコードを例にすると、13行目（Sheet1のCells部分）が以下のようになります。
ThisWorkbook.Sheets(“Sheet1”).Cells(i * 3 + 1, 1) = WB.Sheets(“ツール”).Cells(i + 1, 4)
こうすると、ループ1回目（i=1）が4行目、ループ2回目（i=2）が7行目、ループ3回目（i=3）が10行目、という風にループされていきますよ。
ご参考になれば幸いですm(__)m