エクセルマクロの質問。値の変更のイベントについて

質問

エクセルのマクロを勉強中です。
仕事で使いたいのですが、わからなくて困っています。
教えていただけないでしょうか。

商品受注の表でシート1（商品マスター）に商品マスター（Ａ3に「商品コード」Ｂ3に「商品名」Ｃ3に「型番」Ｄ3に「単価」）があります。

シート2（入力用）は、Ａ3に「商品コード」Ｂ3に「商品名」Ｃ3に「型番」Ｄ3に「単価」Ｅ3に「注文総計」Ｆ3に「Ａ社分」（Ａ社からの受注数）Ｇ3に「Ｂ社分」（Ｂ社からの受注数）Ｈ3に「合計」（Ａ社分とＢ社分の合計）Ｈ3に「チェック」（注文総計と合計の差で同じだと○を表示）があります。

注文総数というのは、Ａ社とＢ社から受注した数の合計で、どちらからいくつ受注したのかは、別に出てくる伝票を数えないとわかりません。

やりたいことは
(1)　「商品コード」を手入力すると、商品マスターから「商品名」「型番」「単価」が入力される。
「商品コード」に該当ないコードを入力すると、メッセージがでる。
(2)「注文総数」と「Ａ社分」と「Ｂ社分」を手入力すると、「合計」と「チェック」が入力される。
「商品コード」の入力があるのに「注文総数」等数値の入力が無い場合は、0とみなして計算する。

以上の(1)と(2)の手入力分が入力（変更）されしだい、自動に入力される分が入力（変更）するようにしたいのです。

今のところ、ボタンを作って(1)と(2)を動かしているのですが、自動にすることもエラー処理部分ができません・・・
ちなみに自分で作った（見よう見まねです）のは以下です。

(1)　Sub 商品マスターコピー()
'
'*** 入力の最後行までを別シートにコピー（vlookup)
'
最終行 = Range("A" & Rows.Count).End(xlUp).Row '入力した商品名の最終行の検索

For 行 = 4 To 最終行
If Range("A" & 行).Value <> "" Then
Range("B" & 行) = Application.WorksheetFunction.VLookup(Range("A" & 行), Worksheets("商品マスター").Range("A:D"), 2, False) '商品マスターから商品名をコピー
Range("C" & 行) = Application.WorksheetFunction.VLookup(Range("A" & 行), Worksheets("商品マスター").Range("A:D"), 3, False) '商品マスターから型番をコピー
Range("D" & 行) = Application.WorksheetFunction.VLookup(Range("A" & 行), Worksheets("商品マスター").Range("A:D"), 4, False) '商品マスターから単価をコピー

End If
Next
End Sub

(2)　Sub 注文数集計()
'
'***　列全てに集計（足し算、引き算、if文）結果を表示
最終行 = Range("A" & Rows.Count).End(xlUp).Row '入力した商品名の最終行の検索

For 行 = 4 To 最終行
If Range("A" & 行).Value <> "" Then
Range("H" & 行) = Application.WorksheetFunction.Sum(Range("F" & 行), Range("G" & 行)) 'H列に（明細あり+明細なし）を表示
' Range("I" & 行) = Range("E" & 行) - Range("H" & 行) 'I列に（総合計-受注数）の差額を表示
Range("I" & 行) = IIf(Range("E" & 行) - Range("H" & 行) = 0, "○", Range("E" & 行) - Range("H" & 行)) '計-受注数）の差額を表示

End If
Next
End Sub

説明が下手なので上手く伝えられたか心配ですが・・・どうぞよろしくお願いいたします。

回答No.1

mimeu · ベストアンサー

自動化

　ボタンを押せば狙い通りに動くようですので、商品コードを入力すれば
ボタンを押さなくても動いてほしい、というご質問だとしてお答えします。

Worksheet_Change イベントを処理すればイケます。
こんな感じです。
　　Private Sub Worksheet_Change(ByVal Target As Range)
　　　　MsgBox Target.Address
　　End Sub
もし商品コードの文字数が一定であれば、バカよけのために
　　If LEN(Target) <> 8 Then Exit Sub などとすればよいでしょう。
ご存じと思いますが、『Private Sub Worksheet_Change(ByVal Target As Range)』
を表示するにはコード編集画面上部の二つのコンボボックスを使います。
またこのコードはシート2（入力用）のコードペインに書きます。

エラー処理

エクセルのヘルプでは
　　　検索の型に FALSE を指定すると、検索値と完全に一致する値だけが検索され、
　　　見つからない場合はエラー値 #N/A が返されます。
とありますので、最初の VLOOKUP の直後に
　　　ワークシート関数の TYPE(Range("B" & 行))=16
　　　またはＶＢＡ関数の VARTYPE(Range("B" & 行))=vbError
でテストできます。

お礼

mimeu様、回答ありがとうございました。
Worksheet_Change イベントを使ってみたら自動化できました。
感激です！
しかし・・・　商品マスターにないコードを入れた場合の処理が上手くできませんでした・・（文字数は一定ではないのです）
しかし、とてもよいヒントをいただけましたので、少し自分でがんばってみます。
お礼が遅くなり申し訳ございませんでした。
とても参考になりました。