文字を入力したセル以降のセルも同じ文字になるVBA

質問

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
ActiveSheet.Protect UserInterfaceOnly:=True
Set R = Union(Range("D5:D38"), Range("E5:E38"), Range("T5:T38"))
With Target
If Intersect(.Cells, R) Is Nothing Then Exit Sub

Application.EnableEvents = False

Range(Cells(.Row, .Column), Cells(38, .Column)).Value = .Value

Application.EnableEvents = True

End With

End Sub

この様なコードがあるのですが範囲を変更したいと思います。

D５：D38は上記コードのままで良いのですが、E5；E38はE5：E36に、T5：T38はT5：T36に変更するにはどうすれば良いのでしょうか？

回答No.1

kybo · ベストアンサー

以下でどうでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim A As Range
Dim I As Integer
ActiveSheet.Protect UserInterfaceOnly:=True
Set R = Union(Range("D5:D38"), Range("E5:E36"), Range("T5:T36"))
With Target
If Intersect(.Cells, R) Is Nothing Then Exit Sub
For I = 1 To R.Areas.Count
If Not Intersect(R.Areas(I), Target) Is Nothing Then
Set A = R.Areas(I)
Exit For
End If
Next I
Application.EnableEvents = False
Range(Cells(.Row, .Column), Cells(A.Cells(A.Rows.Count, 1).Row, .Column)).Value = .Value
Application.EnableEvents = True
End With
End Sub

お礼

この度は僕の質問に時間を割いて頂き誠にありがとうございます。

ちゃんと動作しました、ありがとうございました(*^-^*)

回答No.2

cj_mover

こんにちは。

１■　　.Unionメソッドについて
>　Union(Range("D5:D38"), Range("E5:E38"), Range("T5:T38"))
　今回のオーダーに副った書換えをすれば>
>　Union(Range("D5:D38"), Range("E5:E36"), Range("T5:T36"))
予め解っているセル範囲を参照する場合は、特殊な例外を除いて、
　　Range("D5:D38,E5:E36,T5:T36")
のように、普通にRangeプロパティだけで書きます。
Rangeの引数としての参照文字列が255文字を超えるような特殊な場合には、
.Unionメソッドを使うのが妥当ですが、
今回のようなケースで.Unionメソッドを使うのは場違い、です。

２■　　Worksheet_Changeイベントの条件分岐の基本
大きく分けて２種類の対応があります。
_Changeイベントの引数Targetに返されるセル範囲を基準にして、
　　i )単一セルに対する値変更だった場合だけ処理する
　　ii)複数セルに対する値変更にも対応するよう処理する
この点に注意して、ハッキリと書き分ける（説明する）必要があります。
今回は、単一セルだった場合にだけ処理するケースであろうと思われます。
こういう場合は、
　　If Target.Count > 1 Then Exit Sub
という、お決まりの記述で、
思いもよらず複数セルに対する値変更だった場合に起ってしまうエラーや障害を
予め排除しておくのが普通のやり方です。

３■　　Targetについて
>　Range(Cells(.Row, .Column), Cells(38, .Column)) ...
Target自体がRangeオブジェクトなのですから、
>　... Cells(.Row, .Column) ...
という書き方は無駄ですし、間違いです。
　　... Target ...
でいいです。

４■　　Target以下ｎ行め迄のセル範囲の捉え方について
>　Range(Cells(.Row, .Column), Cells(38, .Column)) ...
これは、（Target.Rowがｎ以下である、という条件の下）
　　Target.Resize(ｎ - Target.Row + 1) ...
という風にRangeオブジェクトであるTargetを基準に参照するのが
普通のやり方です。

５■　　Targetそのものを処理対象から外すことについて
>　Range(Cells(.Row, .Column), Cells(38, .Column)) ...
Targetには既に変更されたばかりの値が設定されている訳ですから、
同じことを２度やる無駄、を減らす意味では、
　　Range(Cells(.Row + 1, .Column), Cells(38, .Column)) ...
と書くべきだったであろうと思います。
前項同様、ｎ行め迄を捉えるには、
　　Target.Offset(1).Resize(ｎ - Target.Row) ...
という風になります。

６■　　シート（クラス）モジュールでのActiveSheetの扱いについて
>　ActiveSheet.Protect UserInterfaceOnly:=True
ActiveSheetでも差し当たり期待通り動く訳ですが、
シートモジュールに関連付けられたシートを参照するなら、
つまり例えば"Sheet1"という名のSheet1モジュールの記述としてSheet1を参照するなら、
Meを使います。ActiveSheetは【似て非なるもの】です。
　　Me.Protect UserInterfaceOnly:=True
です。

７■　　.Protect UserInterfaceOnlyについて
>　ActiveSheet.Protect UserInterfaceOnly:=True
これは不適当です。
まず、目的を確認しましょう。
当該ブックを開いてから、初めて、当該シートの当該範囲（D5:D38,E5:E36,T5:T36）
について値の変更が行われたタイミングから、当該ブックを閉じるまで、
当該シートへの値変更は※手入力を禁止しＶＢＡによるものだけを認める。
　　（　※セルのロックを解除したセルは対象外）
という意味であれば目的には適っています。
ただ、この目的はかなり特殊ですから、
目的と違うことを書いてしまっているように思われるのです。
また、.Protect UserInterfaceOnlyは一度だけ実行するものですから、
目的が合っていたとしても、まだしも。
　　If Me.ProtectionMode = False Then Me.Protect UserInterfaceOnly:=True
のような書き方をするべき処です。
.Protect UserInterfaceOnlyについては通常は、Workbook_OpenイベントやAuto_Open
などのプロシージャで、一度だけ実行するように扱われるものです。
ただコードだけを提示されても、何をやりたいか汲み取ってあげられるものではありませんので、
下部に挙げるサンプルでは、.Protect UserInterfaceOnlyを扱っていません。
そちらで求める処理がどんなものか文章での詳細補足があれば改めて対応を考えます。

８■　　イベントプロシージャの二次的な呼び出しについて
>　ActiveSheet.Protect UserInterfaceOnly:=True
前項で検討した目的が適正だった場合、
または、.Protect UserInterfaceOnlyを適用したシートで
Worksheet_Changeイベントを使用する場合、
には、大きな注意を払う必要があります。
何故なら、手入力での値変更を禁止している訳ですから、
_Changeイベントが呼び出される契機は、他のマクロを実行中のタイミング、
ということになります。
つまり_Changeイベントを呼び出す、元の[ひとつのマクロ]が存在して、
何だかややっこしい構造で処理が連動している、という設計が、
前提になっている、ということになります。
[ひとつのマクロ]での処理として値の変更をして、
値の変更に連れて処理を追加するなら、[ひとつのマクロ]の中ですべて処理して、
むしろWorksheet_Changeイベントをキャンセルするように、
　　Application.EnableEvents = False
　　' 値の変更
　　' 値の変更に連動する処理
　　Application.EnableEvents = True
[ひとつのマクロ]として完結した書き方をするのが基本です。
"イベントプロシージャの二次的な呼び出し"は
初中級者が陥り易い、不可解な誤作動・不正処理・エラーの原因になっている
ことが多いです。
Excelカテゴリでは、比較的よく見かける質問で、
"実物を見ないと解決してあげられない謎の現象についての質問"
というのが一定の割合である訳ですが、
"イベントプロシージャの二次的な呼び出し"が原因であることがかなり多いです。
大抵の場合は、最初書いた時には問題なく動くのですが、
他の処理を追加編集した時に、この設計上の欠陥が初めて露見する、ということです。
ゆくゆくは設計力を高めて、サブル－チン化を含めた構造化を図っていけばいいのですが、
差し当たりは、【マクロからイベントを呼び出さない】ように書いておくように
注意してください。

■■
このカテゴリ常連回答者さんでも平気で間違いを放置している方が多いですが、
（＃その多くは質問者さんが提示したコードの原型を尊重しようという配慮による功罪です）
或いは差し当たり問題は露見しないとか、部分的には正しいとか、条件付きで正しいとか、あっても、
それらを組み合わせたりした時のことまで責任を持って回答することは事実上期待出来ないのです。
＃皆が基本を正しく理解して実践していれば避けられる事も多いのですが。
回答を得たら、ご自身でひとつひとつ確認をして、
注意するべき点を正しく管理できるようにしてくださいませ。

/// 自分ならこういう場合もIntersectは使いませんが元コードを踏襲します
/// 妥当性を持って変数を使わない書き方の一例です

Private Sub Worksheet_Change(ByVal Target As Range)
　With Target
　　If .Count > 1 Then Exit Sub

　　If Intersect(Target, Range("D5:D38,E5:E36,T5:T36")) Is Nothing Then Exit Sub

　　Application.EnableEvents = False
　　Select Case .Column
　　Case 4　'　Ｄ列相当
　　　.Offset(1).Resize(38 - .Row).Value = .Value
　　Case 5, 20　'　Ｅ列、Ｔ列相当
　　　.Offset(1).Resize(36 - .Row).Value = .Value
　　End Select
　　Application.EnableEvents = True
　End With
End Sub

お礼

凄いです、エクセルVBAのプロの方ですね！！非常に勉強になりました。

これからも宜しくお願い申し上げます。