Excel VBAのVLOOKUP関数について

質問

Excel VBAのVLOOKUP関数について

VBAでのVLOOKUPで他のブックを使用する場合の方法がわかりません。
ブックαにて参照したい他のブックβのパスやファイル名、シート、セルは保持しております。
同一ファイル内では取得することができたのですが、他のブックではうまくいきません。

また、VLOOKUP関数にて取得できた場合、取得したセルの下に1段、左に1段目のセルを取得したい場合どうすればいいのでしょうか？

環境はEXCEL2003です。
ご存知の方がいれば教えてください！

回答No.1

keithin · ベストアンサー

方法１：
＞同一ファイル内では取得することができた

と同じ事を，ただし目的の外部ブックを「開いてから」行います
workbooks.open filename:="c:\どこそこの\book1.xls"
msgbox appliaction.vlookup(検索値，workbooks("book1.xls").worksheets("シート名").range("セル範囲"),列番号,FALSE)
workbooks("Book1.xls").close false

方法２：
しばしば「外部ブックを開かずに値を取りたい」と皆さん苦闘します。が，原則として出来ません。
出来ないので出来る方法としてマクロを使い，閉じたブックをVLOOKUPさせる式を目的のセルに書き込ませると出来ます。
必要に応じてその後，数式を記入したセルを同セルの値で置き換えます。

with worksheets("数式のシート").range("数式のセル")
.formula _
="=VLOOKUP(" & 検索値 & "," & フルパスと[ブック名]シート名!セル番地文字列 & ",3,false)"
.value = .value
end with

まず手作業でセルに「VLOOKUPで閉じた外部ブックを参照する正しい式」を書けるように練習し，続いてそれと同じ数式文字列をマクロに書かせるようにしてみてください。
外部ブックを先に開いておいてVLOOKUPの式を書き，正しく結果が出たら続いて外部ブックを保存して閉じます。数式が自動で「外部参照式」に書き換わるので，それを見ながらマクロにその通りの文字列を書かせるよう組んでみると，間違い無くできます。

＞ズレたセルをVLOOKUPしたい
そういう事はVLOOKUP関数では出来ないので，別の関数の組み合わせで求めます。

例：取ってくるのはC列で，検索対象はD列，取ってくるのはヒットした行の１つ下の行
=INDEX(C:C,MATCH(検索値,D:D,0)+1)

といった数式を手で書けるようまずワークシート上で練習し，出来てから同じ式をマクロにします。

お礼

ありがとうございます。

非常に役立ちました！
無事に作成できました！

回答No.2

Wendy02

>同一ファイル内では取得することができたのですが、他のブックではうまくいきません。

マクロの経験の浅い方は、良く考える質問です。本来は、VBAは、やはりブックを開けて、そこから探すというのが、ロジカルです。以下は、可読性が悪いので、一度作ったら、あまり変更できないことが多いです。裏技の一種です。また、Vlookup 関数の被検索範囲は、基本的には、昇順・降順など、きちんと並んでいなければ、正しくは出てきません。また、VBAでは、Vlookup関数などは、ほとんど使われません。

'//
Sub VlookupAvailable()
　　Dim sPath As String
　　Dim sFname As String
　　Dim sSheet As String
　　Dim sRng As String
　　Dim sAdd As String
　　Dim sCol As String

　　Dim sSrch As String
　　Dim n As Long
　　Dim ret1 As Variant
　　Dim ret2 As Variant
　　Dim ret3 As Variant
　　Dim ret4 As Variant

　　sPath = "C:\"
　　sFname = "TestBook1.xls"
　　sSheet = "Sheet1"
　　sRng = "A1:C100"
　　sCol = 2 '検索列
　　sAdd = Range(sRng).Address(1, 1, xlR1C1)

　　sSrch = "1" '検索値
　　If Not IsNumeric(sSrch) Then sSrch = """" & sSrch & """"
　　Debug.Print "VLOOKUP(" & sSrch & ",'" & sPath & "[" & sFname & "]" & sSheet & "'!" & sAdd & "," & sCol & ",FALSE)"
　　ret1 = ExecuteExcel4Macro("VLOOKUP(" & sSrch & ",'" & sPath & "[" & sFname & "]" & sSheet & "'!" & sAdd & "," & sCol & ",FALSE)")
　　If Not (IsError(ret1)) Then
　　　　MsgBox ret1
　　 Else
　　　　MsgBox "見つかりません。マクロ終了", vbExclamation
　　　　Exit Sub '終了
　　End If
　　sAdd = Range(sRng).Columns(CLng(sCol)).Address(1, 1, xlR1C1)

　　If IsError(ret1) = False Then
　　　 If IsNumeric(ret1) = False Then
　　　　　ret1 = """" & ret1 & """"
　　　 End If
　　Else
　　　 Exit Sub
　　End If
　　If Not (IsError(ret1)) Then
　　　　ret2 = ExecuteExcel4Macro("MATCH(" & ret1 & ",'" & sPath & "[" & sFname & "]" & sSheet & "'!" & sAdd & ",FALSE)")
　　　　If Not (IsError(ret2)) Then
　　　　　If IsNumeric(ret2) Then
　　　　　　 n = ret2
　　　　　　 sAdd = Range(sRng).Cells(n - 1, CLng(sCol)).Address(1, 1, xlR1C1)
　　　　　　 ret3 = ExecuteExcel4Macro("'" & sPath & "[" & sFname & "]" & sSheet & "'!" & sAdd)

　　　　　　 sAdd = Range(sRng).Cells(n + 1, CLng(sCol)).Address(1, 1, xlR1C1)
　　　　　　 ret4 = ExecuteExcel4Macro("'" & sPath & "[" & sFname & "]" & sSheet & "'!" & sAdd)
　　　　　　 MsgBox "前は、" & ret3 & vbCrLf & _
　　　　　　　　　　"後ろは、" & ret4
　　　　　End If
　　　　End If
　　End If
End Sub

お礼

ありがとうございます。

苦戦しましたが、完成させることができました！