入力セルの値を一定間隔おいた列に連続コピーするには?

Question 31.2 Previous Next
初めて、お便りします。エクセルについて少し教えてほしいのですが、
セルの連続コピーのVBA方法です。たとえば、B3:AF22のセル枠があります。B10に数字を入れれば、G10(+5)、L10(+10)、Q10(+15)、・・・と、すべてのセル枠内(行)に数字を挿入すれば連続して+5ずつコピーをできれば・・・・と。
そういう方法のVBAがあるでしょうか・・・・ 教えてもらえたらぁ・・ありがたいのですが。
Answer   Copyright (C) 2000.10.7 永井善王
質問の内容からワークシートを作ってみました。
2行目は私の想像で、適当に埋めてあります。A列も適当ですから意味はありません。
疑問を感じたのは AF列です。B列からAF列の間を下図のように5列ごとに区切ると、AF列は余ってしまいます。横計用の列かなと想像しましたが、本題に関係なさそうなので無視して、セル範囲を B3:AE22として進みます。
入力するための列は BからF(コピー元)、GからAE列はコピー先列と考えます。
質問文では 10行目を例えにされていますが、上図の都合で以下の説明では 4行目を使います。

B4セルに入力した数字「5」を、
5列右のG4セルと、
10列右のL4セル、
15列右のQ4セル、
20列右のV4セル、
25列右のAA4セルにコピーする方法は、いろいろ考えられます。その中から3つの案を書いておきます。

A案: 数式で行う方法

この方法はマクロを使いませんが、ユーザーが B3:F22の範囲内のセルに入力すれば、その都度 G3:AE22の範囲内の対応するセル(5カ所)に値がセットされます。

1. G3セルに右の数式を入力する  =IF(B3="","",B3)
2. G3セルをコピーして H3:K3セルに数式を貼り付けする
3. G3:K3セルをコピーして G4:K22セルに数式を貼り付けする
4. G3:K22セルをコピーして、数式を L3:AE22セルに貼り付けする

B案: 入力終了後に一括して写す方法

ユーザーが B3:F22の範囲内に入力している間は、G3:AE22セルには何も反映されません。あらかじめワークシート上にコマンドボタンを作っておいて、それがクリックされたらマクロでコピー貼り付けします。
'==============================================================================
Sub 入力セルから複写セルへ値を写す()
    Range("B3:F22").Copy                          '入力セルをコピーする
    Range("G3:AE22").PasteSpecial Paste:=xlValues '複写セルへ値を貼り付ける
End Sub
'==============================================================================

C案: 入力の都度イベントマクロで写す方法

この案が、ご質問の主旨(文章)に最も合うでしょう。イベントマクロを組めば実現できると思います。
イベントマクロの原理は簡単ですが、マクロを完成させるためには、解決を要する問題が次々と出てくるでしょう。ヒントを書いておきますので、根気よく研究してみてください。なお、イベントについて、ここでは詳しく解説しませんが、もし知りたければ 「ショートカットキーと右クリックを無効にするには」のページを参考にしてください。

イベントマクロは、ワークシートのコード画面で作成します。

1.
ユーザーがセルに値を入力すると Changeイベントが発生するので、それを利用してイベントが発生したセル番号と、入力された値を取得するマクロを作成します。
'==============================================================================
' セルが変更されたときに実行されるイベントマクロ
'------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    行 = Target.Row                             '変更されたセルの行番号
    列 = Target.Column                          '変更されたセルの列名
    値 = Target.Value                           '変更されたセルの値
End Sub
'------------------------------------------------------------------------------
2.
入力後アクティブセルが移動する(注)ことにより SelectionChangeイベントが発生します。上記 1.で取得した値を、同じ行の 5列右のセル、10列右のセル ・・・、25列右のセルにセットするためのイベントマクロを作成します。
(注)[ツール]-[オプション]-[編集]の「入力後にセルを移動する」チェックボックスの設定とは無関係
'------------------------------------------------------------------------------
' アクティブセルが移動したときに実行されるイベントマクロ
'------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range(Cells(行, 列 + 5), Cells(行, 列 + 5)).Value = 値 '5列右のセルに値をセット
    Range(Cells(行, 列 + 10), Cells(行, 列 + 10)).Value = 値 '10列   〃
    Range(Cells(行, 列 + 15), Cells(行, 列 + 15)).Value = 値 '15列   〃
    Range(Cells(行, 列 + 20), Cells(行, 列 + 20)).Value = 値 '20列   〃
    Range(Cells(行, 列 + 25), Cells(行, 列 + 25)).Value = 値 '25列   〃
End Sub
'==============================================================================
基本は以上のとおりですが、ここから先がやっかいです。

まず、SelectionChangeイベントマクロが実行されてセルの値が変更されることにより Changeイベントが発生してしまうことです。
つまり、SelectionChangeイベントマクロの 2行目のマクロが実行された途端に Changeイベントマクロが割り込んで実行されるので、変数「列」には入力セルの列番号ではなくて、複写セル1の中の列番号が取得されてしまいます。
そのために 3行目のマクロが実行されると「複写セル1」の中のどれかの列 + 10列 つまり、「複写セル3」の中のどれかの列に、値がセットされてしまいます。
パズルが好きで暇があるなら「4行目のマクロが実行されると、どの列に値がセットされるか?」解いてみても良いでしょう。しかし本題は、Changeイベントマクロに "セルの値が入力で変更されたのか、マクロで変更されたのかを判別するコードを追加しなければならない" ことに気づくことですから、深入りは不要です。

講義 さらに、入力セルを選択してから値を入力し終るまでの操作は、ユーザーにより、場合により千差万別と言えます。マウスか矢印キーでのセル選択が一般的でしょうが、値を入力しなくてカーソルを次々移動させることがあるでしょうし。数字キーを押した後で [Enter]キーを押す人と、矢印キーを押す人がいるでしょう。よって、マクロ作成者が予期していない Changeイベントまたは SelectionChangeイベントが発生する可能性があります。

イベントマクロの怖さは、あらゆる操作に対応できないと 「マクロエラー」になる恐れがあることです。所詮他人は、自分が想定したとおりに常に動いてくれるとは限らないので、よほど単純明瞭でない限り、 イベントに期待しすぎると墓穴を掘ることになるでしょう。
一応サンプルブックを作っておきましたので、こちらから ダウンロードして試してみてください。うまく動作すれば、あなたが私の想定したとおりに操作したことになります。思うように動作しなければ、想定外の操作をしたと言えます。サンプルブックを開いたときに選択されているセルへ数字を入力してから [Enter]キーを押せば、うまく動くはずです。

せっかく、VBA方法を質問なさったのに水を差すようですが、VBAを使わない A案ではいけないでしょうか。

Excel VBA Macro