『シート名を指定しないでマクロでVlookupをつかうには』（よ〜たん）

よろしくお願いします。

現在、特定のシートでのみVlookupをつかうコードを組んでいます。
それをシートが10〜15程度あるものにも使えるよう応用しようと頑張っていましたが、どんどんわからなくなってきました。

Dim 範囲 As Range

 Set 範囲 = Worksheets("○○○").Range("B3:E100")          
 Dim ws As Worksheet
 Set ws = Worksheets("△△△")                               
    On Error GoTo errMSG

現在は、この　Worksheets("△△△") のところに特定のシート名をいれていますが、応用したいブックのシート名は、毎回変わり、シート数も決まっていません。

どのようにすればいいのでしょうか？

よろしくお願い致します。

----
 どこにVLOOKUPが絡んでくるのですか？

 「こんな事がしたいのか〜」
 と、分かるようなコードを載せるのが良いと思いますよ。

 変数ws　は、VLOOKUP結果を入れるシートなんですかね・・・？

 (HANA)
----
すいません。

現在使っているものです。

イベント利用のため、NEXTは必要ないのだと思いますが、勉強のために作成しました。
そのまま利用していたので、ごちゃごちゃしているかもしれません。

＞変数ws　は、VLOOKUP結果を入れるシートなんですかね・・・？

という風に理解しています。

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Row = 12 Or Target.Row > 19 Then Exit Sub
 If Target.Column <> 21 Then Exit Sub                     'H列27〜36行目のみチェンジイベント発生
 
 Dim 範囲 As Range
 Set 範囲 = Worksheets("○○○").Range("B3:E60")          'コードが見やすいよう範囲を設定。
 Dim ws As Worksheet
 Set ws = Worksheets("△△△")                              'コードが見やすいようシートを設定。
    On Error GoTo errMSG
    
    For i = 13 To 19 Step 2                               'For･･･Nextで回数を指定して繰り返す。

    x = Application.WorksheetFunction.VLookup(ws.Cells(i, "U"), 範囲, 2, False)  'ワークシート関数の
    y = Application.WorksheetFunction.VLookup(ws.Cells(i, "U"), 範囲, 3, False)  'vlookupを使う
    z = Application.WorksheetFunction.VLookup(ws.Cells(i, "U"), 範囲, 4, False)
    
    Cells(i, "V") = x                                      'Cellsを使ってセルを参照する
    Cells(i, "W") = y
    Cells(i, "X") = z

Next i

errMSG:
    Cells(i, "I") = ""                                    'エラーがでたら･･･
    Cells(i, "L") = ""                                    '（Ｈ列が空白だったり、３コードが検索できなかったりしたら）
    Cells(i, "N") = ""                                    'Ｉ．Ｌ・Ｎ列は空白になるように

End Sub

以上です。
上記のコードと検索範囲などは違いますが、複数あるシートも書式は全て同じになっているので、
どのシートにでも使えるようにできたらいいなと思いました。

よろしくお願い致します。
（よ〜たん）

----
 それでしたら、コード内の
 　ws.
 を消してしまえば良いのではないですかね。

 このコードは、各シートのシートモジュールに入れて有るのですよね。
 シート名を指定しない場合は、アクティブなシートのセルを参照しますので
 「自分のシート」と言う事になると思います。

 ws.Cells(i, "U")　のセルと
 Cells(i, "V")　のセルは、同じシートのセルですよね？

 それと、これだとエラーにならなくても
 I,L,N列に「""」が入ると思いますが
 未だ作成途中ですかね？
 それとも、ループの最後で必ずエラーになって終了なのかな？

 (HANA)
----
HANAさん、よろしくお願いします。

errMSG:
    Cells(i, "V") = ""                                   
    Cells(i, "X") = ""                                    
　　Cells(i, "X") = "" 
の間違いでした。

ws．を消せば…というご指摘でしたので、試してみましたが、エラーにはならないのですが、VLOOKUPが反応しませんでした。

チェンジイベントのままだからでしょうか？

不特定のシートに使えるようにということは、チェンジイベントではダメということですよね？

質問ばかりで申し訳ないのですが、もうしばらくお付き合いいただけると助かります。

（よ〜たん）

----
 えっと・・・・

 ＞・・・の間違いでした。
 と言うことではなくてですね。。。

 取り敢えず
 「どう動けばよいのか？」
 とお伺いした方が良いのですかね。

 上のコードでしたら「△△△」のシートモジュールに貼り付けてあるのですよね？

 例えば　U12セルに入力が有ったら
 　V13,W13,X13　に結果が返されれば良いのですか？

 ＞NEXTは必要ないのだと思いますが
 と言う事なので、範囲の該当の結果を書き換える必要は無いのですよね？

 それから
 ＞上記のコードと検索範囲などは違いますが
 ってのは「△△△」以外のシートの場合、
 ＞Set 範囲 = Worksheets("○○○").Range("B3:E60")
 の範囲が違う　って事ですかね？

 因みに、この掲示板は　文頭に半角スペースを入れると
 空行を入れなくても改行がそのまま表示出来ますよ。
_←ここに半角スペースです。

 (HANA)
----
説明が下手ですいませんでした。

＞上のコードでしたら「△△△」のシートモジュールに貼り付けてあるのですよね？
 はい。そうです。
 
＞V13,W13,X13　に結果が返されれば良いのですか？
 はい。別シートにある表から結果をひっぱってきたいと思います。

＞NEXTは必要ないのだと思いますが
＞と言う事なので、範囲の該当の結果を書き換える必要は無いのですよね？
 多分必要になるのでは…。と思います。
 例に出していたコードは応用前の勉強用のものですので、今からつくりたいモノと範囲が違います。
 もっと欲を出していうと、IF文をからませて、Y列が空白でなければVLOOKUP、空白ならば空白のま  ま…。 というふうにして、LOOPさせたいと思っています。

＞ってのは「△△△」以外のシートの場合、
 ＞Set 範囲 = Worksheets("○○○").Range("B3:E60")
 ＞の範囲が違う　って事ですかね？
 はい。その通りです。わかりにくくてすいません。

よろしくお願い致します。


----
 もう少し確認させて下さい。

 今気づいたのですが
 ＞If Target.Row = 12 Or Target.Row > 19 Then Exit Sub
 これでは範囲の指定がおかしくないですか？
 ＞'H列27〜36行目のみチェンジイベント発生
 このコメントも、正しくないですよね。。。

 ＞今からつくりたいモノと範囲が違います。
 だからですかね？

 ＞多分必要になるのでは…。と思います。
 と言うことは、つまり
 U列19行以上のどこかのセルの値が変更になったら
 V,W,X列の13,15,17,19行の、12個のセル全てに　再計算が必要
 と言う事ですか？

 素人考えですとVLOOKUP関数の引数に使ってあるU列のセルが
 変わったところだけ、計算すれば良いのではないかと思うのですが。

 例えば、V13セルのVLOOKUP関数がU13セルを参照するなら
 U13セルが変化したら、V13,W13,X13 は再計算の必要が有りますが
 U15セルを参照しているハズの　V15,W15,X15 は再計算の必要は有りませんよね？

 私がコードを読み違えてますかね。。。？

 (HANA)
----
HANAさん、混乱させてしまい…すいません。
コメント等は忘れてください。イロイロと試したりしていたもので…。
再計算は必要ありません。
その行だけです。

 HANAさんのご指摘のWSを削除して、モジュールを変えて試していました。
 これでよいのかはわかりませんが、vlookup自体はうまくいきました。

Sub 検索()
 Dim 範囲 As Range
 Set 範囲 = Worksheets("検索用").Range("B2:C300")          

 On Error GoTo errMSG
    
    For i = 17 To 60                                     'For･･･Nextで回数を指定して繰り返す。

If Cells(i, "M") = "" Then
   Cells(i, "A") = ""
Else
    x = Application.WorksheetFunction.VLOOKUP(Cells(i, "E"), 範囲, 2, False)      Cells(i, "A") = x                                      
End If

Next i

errMSG:
    MsgBox "登録していない商品があります。                                                                   
End Sub

ですが、エラー処理がうまくいきません。
 Ｍ列が空白なら、空白のまま。
 空白でないのならば、Vlookupを使い、別シートから値をひっぱってくる。
 Vlookupにてひっぱってくる値がなければエラーメッセージを表示させる。

としたつもりだったのですが…。
考え方からしてまちがっていますか？



----
 たぶん、こんな感じでいけると思います。
 シートモジュールに貼り付けて下さい。

 '------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim 範囲 As Range, 入力行 As Long
    If Target.Row < 17 Or Target.Row > 60 Then Exit Sub  '17〜60　以外の行なら　マクロ終了
    If Target.Column <> 5 Then Exit Sub                  'E列　以外なら　マクロ終了
 　    
    Set 範囲 = Worksheets("検索用").Range("B2:C300")
    入力行 = Target.Row
Application.EnableEvents = False
    If Cells(入力行, "M").Value = "" Or Cells(入力行, "E").Value = "" Then
        Cells(入力行, "A").Value = ""
    Else
        On Error GoTo errMSG        'エラーが発生したら　errMSGの行へ移動
        Cells(入力行, "A").Value = Application.WorksheetFunction.VLookup(Cells(入力行, "E").Value, 範囲, 2, False)
    End If                          'A列に　VLOOKUP関数の戻り値を入力
 　
errMSG:
    If Err.Number <> 0 Then
        MsgBox Cells(入力行, "E").Value & "　の登録は有りません。"
        Cells(入力行, "A").Value = ""
        Cells(入力行, "E").Value = ""
    End If
Application.EnableEvents = True
End Sub
 '------

 ＞エラー処理がうまくいきません。
 は「いつでもメッセージが表示される」って事ですよね？
 これが
 ＞それと、これだとエラーにならなくても
 ＞I,L,N列に「""」が入ると思いますが
 って事でした。

 エラーが発生して、その行へ行ったのか
 上から順番にコードが実行されて　その行へたどり着いたのか
 確認して下さい。

 (HANA)