セル範囲に変化があったときに日付けが入るマクロ

質問

お世話になっております。
以前、A1からS１９までのセルに変化があったときに日付けが入るマクロについて、
この掲示板で質問させていただいた者ですが、さらに伺いたいことがあります。

そのときは、以下のプログラムを教えていただきました。

private sub Worksheet_Change(byval Target as excel.range)
　dim h as range
　on error resume next
　for each h in application.intersect(target, range("A1:S19"))
　　application.enableevents = false　
　　cells(h.row, "T") = date
　　application.enableevents = true
　next
end sub

しかし、質問の時点でご指摘のあったことではあったのですが、このプログラムだと
値を変更したとき、セルをダブルクリックして編集可能にしたときだけではなく、
A列からS列に列を挿入したとき、又は列を削除したときも一斉にT列に日付け
が入ってしまいます。

列の挿入・削除の場合は、日付けが入らないようにすることは可能でしょうか？

教えていただけると幸いです。よろしくお願いいたします。

回答No.7

MarcoRossiItaly · ベストアンサー

あるとき例えば 1 列を挿入したら、その後は T 列ではなく、U 列に日付を記入するようになっていてほしいということですよね？

列を挿入したとき、シート上にある何かの数式中のセル参照は、絶対参照と相対参照のどちらであっても、自動的に番地が変化していきますね。なので上のお話をされているのであれば、絶対参照云々とはちょっと違う気もしますが…。絶対参照にすると動かないというのは、数式を記入してあるセルをコピーして他所に貼り付ける場合などですね。

Excel という機械であれ人間であれ、1 列挿入したらその後は U 列に記入すべきなんだということが判断できるためには、何らかの基準を持っていないといけませんね。基準となり得る情報は様々考えられると思いますが、一例としては、近くにある特定のセルを見て、それが T 列から U 列に動いたので、今は U 列に記入すればいいんだなと分かるとか。

例えば T1 セルに「日付」という文字列があり、それが U 列なり S 列に動いても、その新しい位置を探して日付データを記入させるなら、例えば下のような感じで書けるでしょう。（今回 Range("A1:S19") を Range("A2:S19") と書き換えてあります）

必要であれば、Range("A2:S19") という範囲も同様にして目印を探すことにより、可変にすることもできるでしょう。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim r0 As Range, r1 As Range, r2 As Range
　　Set r0 = Rows(1).Find(what:="日付", lookat:=xlWhole)
　　Set r1 = Intersect(Target, Range("A2:S19"))
　　If r0 Is Nothing Or r1 Is Nothing Or Target.Address = Target.EntireColumn.Address Then Exit Sub
　　For Each r2 In r1
　　　　If r2.Value <> "" Then　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　　　　　Application.EnableEvents = False
　　　　　　　　Cells(r2.Row, r0.Column).Value = Date
　　　　　　Application.EnableEvents = True
　　　　End If　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　Next r2
End Sub

お礼

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

この度は親切に教えていただき、大変助かりました。

回答No.6

MarcoRossiItaly

何度も申し訳ありません。

「Target.Count >= Rows.Count」という条件を「Target.Address = Target.EntireColumn.Address」と書き換えても、複数の列の挿入・削除のケースを処理対象から除外できますね。EntireColumn は、Target が属する列全体という意味です。このほうがセル数に関係ないので、ベターかもしれませんね。

こちらも修業中の身ですので。すみません。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim r1 As Range, r2 As Range
　　Set r1 = Intersect(Target, Range("A1:S19"))
　　If r1 Is Nothing Or Target.Address = Target.EntireColumn.Address Then Exit Sub
　　For Each r2 In r1
　　　　If r2.Value <> "" Then　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　　　 Application.EnableEvents = False
　　　　　　 Cells(r2.Row, "T").Value = Date
　　　　 Application.EnableEvents = True
　　　　End If　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　Next r2
End Sub

お礼

ありがとうございます。

ところで、１点伺って良いでしょうか。

「Cells(r2.Row, "T").Value = Date」の部分についてですが、
T列を絶対参照にすることはできますか？
今の状態だと、列を挿入・削除してもT列のままになってしまいます。

通常の関数であれば「＄」をつければ絶対参照になりますが、VBAの場合は
どのように記述すれば良いか、ご存知でしょうか。

回答No.5

MarcoRossiItaly

No.4 です。度々すみません。書き漏らしたことを。

複数セルにコピペするという場合も、ループしないといけませんね。

＞お手数おかけしますが、よろしくお願いいたします。

単にコード教えてくださいというよりも、むしろこのようなご質問のほうが回答者としてはモチベーションが高まります。

お礼

ありがとうございます。
親切な対応、感謝します。

回答No.4

MarcoRossiItaly

＞「Target.Count >= Rows.Count 」とはどういう意味なのでしょうか？rowは行のことだと思いますが、この記述で列の挿入・削除に対応できているということでしょうか。

まず Target について。VBE（Visual Basic Editor）画面が開いているときにキーボードの F1 を押すと表示されるヘルプで、Worksheet.Change イベント（セルの値の記入・クリア、列の挿入・削除といった変更）を検索すると、パラメータ Target は、Range 型の変数であり、「変更された範囲」（シートに変更があった瞬間に選択されているセル範囲）を表すと書いてあります。

続いて range を検索すると、Range オブジェクトや Range オブジェクト メンバ（オブジェクトに用意されているメソッドとプロパティ）のページが見付かります。そして、Range.Count プロパティ（セル範囲中にあるセル数）というものがあることが分かります。

VBA において、Rows と Row のような単数と複数の単語は、異なるオブジェクトを表します。厳密に区別してください。

Row は、Row オブジェクト（単一の行）。Rows オブジェクトは、Row のコレクション（集合）です。Worksheet.Rows プロパティにより特定されるオブジェクトです。そのヘルプにも説明があるとおり、「そのシートの全ての行」を表します。Worksheet が ActiveSheet である場合は、「ActiveSheet.」という記述を省略し、単に Rows と書くことができます。

Rows(1) というふうにインデックスを付けると、Rows オブジェクトではなく、「行番号 1 の行」という Row オブジェクトを表します。Rows("1:2") のようにアドレスを付ければ、2 行のオブジェクトですね。単数で Row(1) と書くのは、誤りです。

Rows.Count プロパティは、シートの（セル数ではなく）全ての行数を表します。Excel 2003 以前では 65,536、2007 以後では 1,048,576 という数値です。

以上により、ご質問の不等式は、「選択している範囲中のセル数がシートの行数以上」、すなわち「挿入・削除などのために 1 列以上の全体を選択している状態」（通常の操作の場合）かどうかを判定しようとしていることが分かります。条件に該当すると、Exit Sub（残りのコードを実行せずにプロシージャを終了）することになります。

なお r1 Is Nothing は、Target と Range("A1:S19") に重なりがないことを意味します。

＞r2については、範囲指定（定義）していないように見えるのですが、どういった役割を持つ変数なのでしょうか。

r2 は、r1 中の各セルを表します。なぜこれが出てくるのかと改めて問われると困るのですが、For Each ... Next の構文を使うには必ず、型の合った適当な変数を用意してあげる必要があるということです。インターネット上に無数の情報が転がっているとは思いますが、例えば参考 URL でお勉強してみてください。

＞また、r1に続き、繰り返しの処理をする理由はなんでしょうか。

セルへの値の記入などを行ったとき、選択している範囲が 1 セルのみであった場合は、r1 と r2 が同じセルを表すことになります。1 回のみのループとなるので、ループする必要はありません。複数のセルを選択した状態で値をタイプし、Ctrl を押しながら Enter すると、選択していた全セルに同じ値が記入されますね。その場合は r1 中の各 r2 について T 列への日付の記入という処理を繰り返すため、ループにしています。

＞なお、前に答えてくださった方は、エラー発生のケースも説明してくださいましたし、その場合もコードも書いてくださいました。

なるほど。

参考URL：

http://officetanaka.net/excel/vba/statement/ForEachNext.htm

お礼

親切に詳しく教えていただき、ありがとうございます。

何度も拝読して、ようやくわかってきた感じがします。

すっきりしました。

回答No.3

keithin

とりあえず
＞セルをダブルクリックして編集可能にしたときだけではなく…

というお話なので、先のご相談への回答で何回か出てきたのを一部盛り込んで応用し

private sub Worksheet_Change(byval Target as excel.range)
　dim h as range
　if target.count > 1 then exit sub
　on error resume next
　for each h in application.intersect(target, range("A1:S19"))
　　cells(h.row, "T") = date
　next
end sub

ぐらいしておけば十分です。

まぁ繰り返しにはなりますが「列・行の挿入」も「編集」という意味では一緒なので、今回ご質問の状況のようにマクロは同様に動作します。
「この編集」はＯＫで「こういう編集」は対象外にしたいと使いながら見えてきたら、そのようにマクロを改修していけば良いというお話です。

＃余談ですが
教わったマクロを元に新たなご相談が発生した際は、「教わった回答からコピペして質問」するんじゃなくて、「それを元にいま実際にあなたのエクセルで動かしてるマクロ」の方をご自分のエクセルからコピーして掲示し、情報提供してください。回答を元にあなたが勝手にどこか書き換えたのが原因で、新たなご相談の状況が発生している可能性だってあるのですから。

お礼

前回に続き、ありがとうございます。

複数のセルを操作した場合はマクロが起動しない、という記述を加えたのですね。

勉強になりました。

＞＃余談ですが

以後、そのようにいたします。

回答No.2

MarcoRossiItaly

No.1 です。すみません、「複数の」列の挿入・削除に備えて「=」を「>=」に書き換えたので、次のコードに差替えをお願いします。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim r1 As Range, r2 As Range
　　Set r1 = Intersect(Target, Range("A1:S19"))
　　If r1 Is Nothing Or Target.Count >= Rows.Count Then Exit Sub　　'列の挿入・削除への対策を追記
　　For Each r2 In r1
　　　　If r2.Value <> "" Then　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　　　　　Application.EnableEvents = False
　　　　　　　　Cells(r2.Row, "T").Value = Date
　　　　　　Application.EnableEvents = True
　　　　End If　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　Next r2
End Sub

お礼

親切にありがとうございます。

しかし、VBAの初心者でしてわからない点があるので、教えていただけると助かります。

＞　　If r1 Is Nothing Or Target.Count >= Rows.Count Then Exit Sub　　
'列の挿入・削除への対策を追記

「Target.Count >= Rows.Count 」とはどういう意味なのでしょうか？
rowは行のことだと思いますが、この記述で列の挿入・削除に対応できているという
ことでしょうか。

＞　　For Each r2 In r1
　　　　　　　　Application.EnableEvents = False
　　　　　　　　Cells(r2.Row, "T").Value = Date
　　　　　　Application.EnableEvents = True
　　　Next r2

r2については、範囲指定（定義）していないように見えるのですが、
どういった役割を持つ変数なのでしょうか。
また、r1に続き、繰り返しの処理をする理由はなんでしょうか。

お手数おかけしますが、よろしくお願いいたします。

回答No.1

MarcoRossiItaly

あまりお勧めしにくいというか、わざと？手を抜いたコードを採用されたのですね。On Error Resume Next は、原因の特定が困難なエラーが発生しそうな場合に、仕方なく使うといった感じが望ましいです。つまり、なるべく事前に原因をつぶしておき、それらに対応するための処理をコードに含めるのが基本です。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim r1 As Range, r2 As Range
　　Set r1 = Intersect(Target, Range("A1:S19"))
　　If r1 Is Nothing Or Target.Count = Rows.Count Then Exit Sub　　'列の挿入・削除への対策を追記
　　For Each r2 In r1
　　　　If r2.Value <> "" Then　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　　　　　Application.EnableEvents = False
　　　　　　　　Cells(r2.Row, "T").Value = Date
　　　　　　Application.EnableEvents = True
　　　　End If　　'「ユーザーがセルをクリアした場合」であっても日付を記入したければ、この行を削除
　　Next r2
End Sub

お礼

書いてくださって、大変ありがとうございます。

なお、前に答えてくださった方は、エラー発生のケースも説明してくださいましたし、
その場合もコードも書いてくださいました。