こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excel VBAのVLOOKUP関数について

Excel VBAのVLOOKUP関数について

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

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

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

投稿日時 - 2010-05-10 17:08:04

QNo.5885486

すぐに回答ほしいです

質問者が選んだベストアンサー

方法1:
>同一ファイル内では取得することができた

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


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

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

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




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

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

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

投稿日時 - 2010-05-10 17:32:09

お礼

ありがとうございます。

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

投稿日時 - 2010-05-25 17:04:18

ANo.1

このQ&Aは役に立ちましたか?

9人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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

マクロの経験の浅い方は、良く考える質問です。本来は、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

投稿日時 - 2010-05-11 01:36:23

お礼

ありがとうございます。

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

投稿日時 - 2010-05-25 17:05:27

あなたにオススメの質問