シート1のセルの値がシート2の範囲にあったら

質問

度々お世話になっております。
またＶＢＡについてご教授願いたく思い、質問させて頂きます。

ざっと状況を説明しますと、
シート「メニュー」には保存先のフルパスを入力する「保存先」というセルと、
コードを入力する「検索コード」というセルがあります。

シート「リスト」には今まで「検索コード」セルに入力されたものが転記されていきます。

今回教えていただきたいVBA(マクロ)は、
シート「メニュー」のセル名「検索コード」にコードを入力してエンターを押すと実行します。

(1)その時、シート「メニュー」のセル「検索コード」のコードが、
シート「リスト」の(A2:A65536)範囲名「リスト」にあった場合は、
シート「メニュー」のセル「保存先」のフルパス＆セル「検索コード」＆".xls"でファイルを開く。

以上の処理を、以下のVBAの☆の部分に入れたいのです。

Sub Test1()
Dim pt
Dim Fn

If Range("検索コード") = "" Then
Exit Sub
End If

　　If ☆(Then)　　
Eles

Worksheets("メニュー").Range("検索コード").Copy
Worksheets("リスト").Range("A60000").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

With Sheets("メニュー")
pt = Range("保存先").Value
Fn = Range("検索コード").Value
End With

Sheets(Array("フォーマット")).Copy
ActiveWorkbook.SaveAs Filename:=pt & Fn & ".xls"
End If
End Sub

当方で試行錯誤してみましたものの、シートを超えてのRangeや範囲からの検索、セル参照からのファイルを開くなど、上手くいきませんでした。

また、上記以外の方法として『「保存先」パスに「検索コード」のxlsファイルがあったら開く』という条件文でも構いません。
こちらもやってみたのですが、上手くいきませんでした。

不明瞭なところがあれば補足致します。
初心者故知識が少ないのですが、ご教授宜しくお願い致します。

回答No.1

nda23 · ベストアンサー

>シートを超えてのRange
オブジェクトを明示的に指定しないからです。
デフォルトオブジェクトを使ったプログラムは
プロから見ると品質上、即却下です。

先ず、シートを指定する変数を作りましょう。
Dim メニュー As WorkSheet
Dim リスト As WorkSheet
Set メニュー = ThisWorkbook.WorkSheets("メニュー")
Set リスト = ThisWorkbook.WorkSheets("リスト")

この後、検索コードを取得します。
Dim 検索コード As String
検索コード = メニュー.Range("検索コード").Value
If 検索コード = "" Then Exit Sub

★If Range("検索コード") = "" Then のように対象の
シートを指定しないコーディングは危険です。
できればプロパティもデフォルトではなく、明示的に
指定すべきです。

検索は以下の通りです。
Dim 検出コード As String
On Error Resume Next
検出コード = Application.WorksheetFunction.VLookup _
(検索コード, リスト.Range("A2:A65536"), 1)
On Error GoTo 0

If 検出コード <> "" Then
　　Dim ブック名 As String
　　ブック名 = メニュー.Range("保存先").Value
　　If Right(ブック名, 1) <> "\" Then ブック名 = ブック名 & "\"
　　ブック名 = ブック名 & 検出コード & ".xls"
　　'ブックを開く
　　Dim 対象ブック As Workbook
　　On Error Resume Next
　　Set 対象ブック = Application.Workbooks.Open(対象ブック)
　　On Error GoTo 0
　　If 対象ブック Is Nothing Then
　　　　'★ファイルが存在しない場合の処理
　　Else
　　　　'★ファイルを開いた後の処理
　　End IF
Else
　　リスト内に同じものが無い場合の処理
End If

あと、変に省略した短い英字の変数名より、日本語の
変数名の方が分かり易いですよ。

お礼

お世話になっております。
nda23さんに頂いたものをほぼそのまま使ってみたり、Dirで作り変えてみたり等、試行錯誤しましたが上手くいきませんでした。

問題は、「検索コード」をシート「リスト」のセル範囲名「リスト」から探すところにあるようで、
リストにあるはずのコードが結果「""」として出てきていました。
そして検出コード=""になってしまい、どうやってもリストと一致せず、「リスト内に同じものかない場合の処理」というルートにしか進んでいませんでした。

-----
Dim メニュー As Worksheet
Dim リスト As Worksheet
Dim 検索コード As String
Dim コードリスト As Range
Dim 検出コード As String
Set メニュー = ThisWorkbook.Worksheets("メニュー")
Set リスト = ThisWorkbook.Worksheets("リスト")
検索コード = メニュー.Range("検索コード").Value
Set コードリスト = リスト.Range("リスト")

On Error Resume Next
検出コード = Application.WorksheetFunction.VLookup(検索コード, コードリスト, 1, 1)
On Error GoTo 0

If 検出コード <> "" Then
-----
以上の構文で、「検索コード」が「100000」で「コードリスト」に「100000」があるはずなのに、「検出コード」が「""」で返ってきてしまいます。

何故なのでしょうか？；

補足

ご回答ありがとうございます。
シートからセルまで事細かを明示的に記述する方法や、
関数でいうところのVlookupのVBAでの記述の仕方がわからず悩んでいました。ありがとうございます。

変数名はアルファベットに限らなくても良いのですね＠＠
よく見るのは英単語であったり英単語の省略であったりしたので、特に気にもせず「ａ」でも「ｂ」でもとりあえず自分が把握できればいいか、と考えていました。
第三者に見てもらうことを考えてもわかりやすい方がいいですね。

教えて頂いた構文を組み込んでみたのですが、エラーやファイルを開いてくれないなど、ケースごとの処理も入れてみたのですが引っかかってしまいます。

ですがこの頂いた構文を自分でアレンジしてみれば動きそうな気配がするので、またしばらく自分で考えてみます。
お礼が遅くなってしまいますが、試行錯誤して作り変えてみたいと思います。
予想としては、Dirを使ってみる予定です。
とりあえずはその旨をと思いまして、補足とさせて頂きます。

解決しましたらベストアンサーとしてお礼を、
もしアレンジしてもできなかった場合は、またその問題点をお礼に記述させて頂きたいと思います。