vlookup関数の引数を変数で指定することはできますか？

質問

エクセルのVBAでマクロを組み、同じフォルダ内にあるファイルにvlookup関数でリンクを張ろうかと考えています。

ファイルの構成は以下の通りです。

マクロを組んでいるファイル：Book1.xls
マクロが適応されるファイル：Book2.xls
vlookup関数で参照されるファイル：Book3.xls

実際にBook1に組んだマクロは以下の通りです。

Workbooks.Open ThisWorkbook.Path & "\Book2.xls"
Workbooks("Book2.xls").Activate

myFile = ThisWorkbook.Path & "\" & "Book3.xls"

b = 2
Do Until ActiveSheet.Cells(b, 1) = ""

ActiveSheet.Cells(b, 6) = "=VLOOKUP(E" & b & ",myFile!$A:$F,5,0)"

b = b + 1
Loop

上記マクロで実行すると、Book2が開き、vlookup関数が挿入されるところまではいきますが、
変数myFileが正常に変換されないらしく、ファイルが見つからない状態となってしまいます。
myFileの代わりにフルパスでファイルをしていすると正常にマクロは起動することは確認しましたが、
毎回作業するフォルダが変わる為、フルパス指定はできるだけ避けたいです。

基礎的な質問で恐縮ですが、
vlookup関数の引数（参照範囲）を変数で指定する方法を教えてください。
よろしくお願いいたします。

回答No.4

Wendy02 · ベストアンサー

こんばんは。

数式を入れる時には、ActiveSheet.Cells(b, 6)　のような暗黙のプロパティはやめたほうがよいです。以下は、FormulaLocal を使用していますが、通常、Formula だけで、R1C1 方式でも入力可能です。また、変数というなら、変数の宣言の習慣は付けたほうがよいですね。

実際のマクロはエラー処理や開いたブックの場合の二重のオープンの部分を避けるようにはしますが、テスト用として割愛しておきます。

相対参照の数式の場合は、ループは使いません。範囲を選択して、同じ数式を入れればよいです。その場合は、R1C1 方式のほうが便利です。

'標準モジュール

Sub Test2()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim myFile As String
　　On Error Resume Next
　　Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")
　　Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\Book3.xls")
　　myFile = "[" & wb1.Name & "]Sheet1"
　　wb1.Activate '本来は、ここが曖昧。Sheet の指定が必要
　　With Range("A2", Range("A65536").End(xlUp))
　　　 If .Count > 1 Then
　　　　.Offset(, 5).FormulaLocal = "=VLOOKUP(RC[-1]," & myFile & "!C1:C6,5,0)"
　　　 End If
　　End With
　　Set wb1 = Nothing: Set wb2 = Nothing
End Sub

回答No.5

imogasi

VBAとVLOOKUPの組み合わせは余り必要ない。
Findメソッドなどによって検索が行われればすむ。
ーー
VLOOKUP関数の第１引数は、即実行するため使うときは
E" & b & ",
になっているが、Range("E" & b),とでもなるのではないか？
シートの関数の場合はE3と書いても、VBAで使うときはRange("E3")のような書き方が必要と思うが。
ーー
「検索範囲のあるファイル」のセル範囲は、そのアリ場所のブックを変えられるように、考えているようにしているのは良いとして、シートの指定はどうなっているのか。
他ブックを指定したら式を入れるシートであってもシート名を
指定しないといけないのでは。
ーー
また、検索範囲のあるファイルのセル範囲は、
これもRabge(・・・）で指定しないとダメでは。
ーー
今までの経験から、こんなこが問題かと思うが、質問の書き方が荒くて（セルに式の挿入の問題か？こういうのはリンクというのかな？式を挿入したりしないでVBAで処理できるのでは）
式をVBAで埋め込むなら、Ccells(・・）.Formula="式”のようになるはず。上記ならValueとして文字列を入れているのでは。
例
Sub test01()
Cells(1, 1).Formula = "=A2+A3"
End Sub
ーー
質問の標題を文字通り受け取ると、
＞vlookup関数の引数を変数で指定することはできますか
VLOOKU関数には４つ引数が有る。
第何引数のことかを書かないと質問にならない。

回答No.3

hige_082

＞ご指摘の通り、""(ダブルクォーテーション)で囲むと変数と宣言していても、文字列と判断されてしまいます。

ActiveSheet.Cells(b, 6) = "=VLOOKUP(E" & b & ",myFile!$A:$F,5,0)"

この行しかありませんが

回答No.2

okormazd

下記のようではだめなのか。

Sub test()

Workbooks.Open ThisWorkbook.Path & "\Book2.xls"
Workbooks.Open ThisWorkbook.Path & "\Book3.xls"
Workbooks("Book2.xls").Activate

myFile = "[Book3.xls]Sheet1"

b = 2
Do Until ActiveSheet.Cells(b, 1) = ""

ActiveSheet.Cells(b, 6) = "=VLOOKUP(E" & b & "," & myFile & "!$A:$F,5,0)"

b = b + 1
Loop

End Sub

回答No.1

n-jun

変数を""で囲ってしまっては単に数式の文字列になってしまいませんか？

補足

回答ありがとうございます。
ご指摘の通り、""(ダブルクォーテーション)で囲むと変数と宣言していても、
文字列と判断されてしまいます。

ただ、ご指摘の""で囲っているとはどこの部分を指しているのでしょうか？
今回変数を使用したいのは、ファイル名のmyFileと言う変数です。

分かりにくくて申し訳ございません。