エクセル2013VBA Changeイベント 選択

質問

お世話になります。一度質問を締め切ったのですが、色々やっているうちに問題がでてきたので再投稿いたしました。Excel2013です。

計画のファイルがあります。
例えば、"テーブル"シートのA列に品物A、品物B、品物C・・・、と入力されていて、B列にはA列の品物に対応して、付属A、付属B、付属Aなどと入力されているとします。実際は、品物数は数千種類あります。（この例の場合は、品物Aと品物Cは同じ付属を使う）

"計画"シートには、A列にデータの入力規則のリストより、A社やB社・・・を選択し、
さらにB列は入力規則のリストより、=INDIRECT(B2)などといれて、A列の入力規則に対して品物Aや品物B・・・などを選択します。選択するようにしています。
それで、品物Aを選んだら、その下の行に付属Aとか自動ででるようにしたいと思っています。
（列は増やしたくありません）

入力範囲のどこでも選択をできるようにしておきたいので、入力範囲にはすべてリストが設定されており、数式を入れることはできません。
とりあえず、品物を入力する範囲は、C2:C25,G2:G25範囲です。

その状態で、
とりあえずご教授いただいて下記コードまでたどりついていき通常は大丈夫なのですが、B列とC列をまとめて選択コピーして貼り付けると、付属Aと出るべきところが何も起こりません。アクティブセルが隣の列を選択しているためだと思われますが、どうすればよいか分かりません。

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo mExit

Application.EnableEvents = False
If Intersect(Target, Range("C2:C25,G2:G25")) Is Nothing Then

Application.EnableEvents = True
Exit Sub
Else
If Target.Offset(1, 0).Value = "" Then

Target.Offset(1, 0).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheets("テーブル").Range("B:C"), 2, False)

Application.EnableEvents = True
End If

End If
Application.EnableEvents = True
Exit Sub
mExit:
Application.EnableEvents = True

End Sub

度々お手数をおかけいたしますが、ご教授ください。

回答No.1

keithin · ベストアンサー

＞B列とC列をまとめて選択コピーして貼り付けると、付属Aと出るべきところが何も起こりません

イベントプロシジャが想定している以外の使い方をしてると（イベントプロシジャの設計想定にヌケがあると）、こういう不具合が起こります。

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("C2:C25,G2:G25"))
　　application.enableevents = false
　　h.offset(1) = application.worksheetfunction.vlookup(h.value, worksheets("テーブル").range("B:C"), 2, false)
　　application.enableevents = true
　next
end sub

＞B列は入力規則のリストより、=INDIRECT(B2)などといれて、
＞A列の入力規則に対して品物Aや品物B・・・などを選択します。選択するようにしています。

こんな具合に間違った事がご相談に書かれているので、ご相談で書かれてる内容のどれがホントでどれが間違いなのか、全て疑って解明してか無きゃならないのがとっても大変です。

＞物Aを選んだら、その下の行に付属Aとか自動ででるようにしたいと思っています。
＞（列は増やしたくありません）

入力欄C2:C25,G2:G25には入力規則で許可している「品物ＡＢＣ」だけじゃなく、入力規則では許可していない「付属品ＡＢＡ」もまたC2:C25,G2:G25の中に入力したいと、一種矛盾した説明になってます。でもまぁ、この部分はホントであると（付属品ＡＢＡも入力できるよう、入力規則を設定しているとの説明がヌケていると）解釈します。

お礼

「品物ＡＢＣ」をリストから選べますが、「付属品ＡＢＡ」も入力できるよう、入力規則を設定できています。
抜け、もれ、間違いありで余計なことを考えさせてすいません。
このコードでうまくいきました。一応色々な使い方を適当に試しましたが、問題は発生していません。
どうもありがとうございました。助かりました。