VBAでChangeイベントを使いたい

質問

今エクセルで出納を作ってます。
シート1には　Ａ日付　Bコード　C金額　D　消費税区分　E　金額
F,G,H,Iにも同様に貸方科目を入れてます。
シート2にはAコードB科目を上から下にずっといれてます。

それで借方金額Ｃの金額をEに飛ばすこと
VLOOKUPでＢのコードに対応する科目を表示すること
上記をChangeイベントでやりたいのですが、金額転記はうまくいったのですが、
VLOOKUPの方が標準モジュールではうまくいくものの、シートモジュールに移すとうまく
行きません。おそらく根本的な理解がかけてるからだと思います。

今の記述は下記
シート1に

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 1 Or Target.Row > 100 Then Exit Sub
If Target.Column <> 5 Then Exit Sub

Dim Cnt As Long
For Cnt = 2 To 100
Range("I" & Cnt).Value = Range("E" & Cnt).Value
Next Cnt

If Target.Row = 1 Or Target.Row > 100 Then Exit Sub
If Target.Column <> 3 Then Exit Sub

End Sub

標準モジュールに
Option Explicit

Sub 科目()

Dim シート1 As Worksheet
Dim シート2 As Worksheet

Set シート1 = Worksheets("出納")
Set シート2 = Worksheets("科目")

Dim myR
On Error GoTo ErrorHandler
myR = Application.WorksheetFunction.VLookup(シート1.Range("B2"), シート　　　　　　　　　2.Range("A2:B87"), 2, False)

シート1.Range("C2").Value = myR
Exit Sub

ErrorHandler:
シート1.Range("C2").Value = "該当無し"

End Sub

　大変素人な質問ですみませんが、ご回答いただけると嬉しいです。
　基礎の本やレファレンス本は見たのですが、標準モジュールでできること
　がなぜシートモジュールでできないかが全く分かりません。
　よかったらお教えください。

回答No.4

TAKA_R

１です。
If Target.Row = 1 Or Target.Row > 100 Then Exit Sub
If Target.Column <> 5 Then Exit Sub
というコードは確かに範囲を示してはいますが、「１～１００の行でなかったら、このプロシージャから出て行ってね。5の（eの）列でなかったら、このプロシージャから出て行ってね」と書いています。
なので、複数のchangeを書くには、コードを書く順番が重要になります。
「でなかったら」ではなく、「だったら」と書くほうが、楽だと思います。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 1 Or Target.Row > 100 Then Exit Sub
If Target.Column =5 Then
Range("I" & Target.Row).Value= Range("E" & Target.Row).Value

ElseIf Target.Column = 2 Then
If not Target.Value="" Then
Dim シート2 As Worksheet
Set シート2 = Worksheets("科目")

On Error GoTo ErrorHandler
Target.Offset(,1).Value = Application.WorksheetFunction.VLookup(Target.Value, シート2.Range("A2:B87"), 2, False)

Exit Sub

ErrorHandler:
Target.Offset(,1).Value = "該当無し"
End if
End if
End Sub

動作確認はしていませんが、イベントで作りたいということなので、「こういうことかな？」みたいな感じです。
Callでサブプロシージャから呼び出しても使えますが、再利用の予定がないと「かさばり」ますしね。
「changeに対応させる」というのは、Targetに押し込むというか・・・。

回答No.3

queuerev2

とりあえずこちらの環境(Excel 2003, Windows XP SP3)では以下のコードで動作しました。
重要な変更点は１つだけで、質問者様の書かれたコードのうち
If Target.Column <> 3 Then Exit Sub
のThen以降を終了からVlookup実行に変えただけです。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

　　If Target.Row = 1 Or Target.Row > 100 Then Exit Sub
　　If Target.Column = 5 Then

　　　　Dim Cnt As Long
　　　　For Cnt = 2 To 100
　　　　　　Range("I" & Cnt).Value = Range("E" & Cnt).Value
　　　　Next Cnt

　　ElseIf Target.Column <> 3 Then

　　　　Dim シート1 As Worksheet
　　　　Dim シート2 As Worksheet

　　　　Set シート1 = Worksheets("出納")
　　　　Set シート2 = Worksheets("科目")

　　　　Dim myR
　　　　On Error GoTo ErrorHandler
　　　　myR = Application.WorksheetFunction.VLookup _
　　　　　　　　　　(シート1.Range("B2"), _
　　　　　　　　　　シート2.Range("A2:B87"), 2, False)

　　　　シート1.Range("C2").Value = myR

　　End If
　　Exit Sub

ErrorHandler:
　　シート1.Range("C2").Value = "該当無し"

End Sub

回答No.2

queuerev2

>VLOOKUPの方が標準モジュールではうまくいくものの、シートモジュールに移すとうまく
>行きません。

どのようにうまくいかないのでしょうか。
エラーが出るのか、Vlookupの結果が変な値になるのか、Vlookupが実行されていない様子なのか、フリーズしてしまうのか、などが考えられますが。

また、プロシージャを置く場所以外に違いはあるのでしょうか。
たとえばの話ですが、シートモジュールに移す際には「科目」プロシージャごとではなくその中身を「Worksheet_Change」プロシージャの先頭に挿入する、などを行っているのでしょうか。

補足

Sub 科目()　と　End Sub　の間の記述をシートモジュールに入れ込みました。

回答No.1

TAKA_R

コードをきちんと読んでいないので、はっきりとは分からないのですが。
質問は「どうやったらイベントプロシージャを動かせるのか」かなと思いました。
もし標準モジュールがきちんと書けているなら、イベントプロシージャ側に
Ｃａｌｌ　プロシージャ名（引数）
を書き込めば、外にあるプロシージャ（イベントプロシージャやモジュール外にあるprivate プロシージャ以外）が呼び出せます。
この場合
Ｃａｌｌ　科目()
ですね。
もちろんイベントプロシージャにコピペしてもいいですけど。

あとは、changeする部分をきちんと対応させないといけません。
イベントが発生したのに何も変わらなかったら、イベントを発生させる意味がありませんし。

イベントプロシージャ内の最後のif文2行は、必要ありませんよね？？

お礼

ありがとうございます。返事が遅くなってすみません。

>あとは、changeする部分をきちんと対応させないといけません。

If Target.Row = 1 Or Target.Row > 100 Then Exit Sub
If Target.Column <> 5 Then Exit Sub

これがそうした部分の範囲指定になるのでしょうか？
これを入れると動いたので。