No.1ベストアンサー
- 回答日時:
方法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)
といった数式を手で書けるようまずワークシート上で練習し,出来てから同じ式をマクロにします。
No.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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- その他(Microsoft Office) エクセルについて教えてください。 2 2022/10/20 14:55
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Excel(エクセル) マクロVBA別Excelブックにデータ転記 2 2022/07/10 23:35
- Visual Basic(VBA) EXCEL VBA 単語置き換え について質問です ブック名 ぶぶぶ シート名 ししし セル V3〜 3 2023/03/08 01:41
- Visual Basic(VBA) Excelのマクロコードについて教えてください。 1 2022/03/27 13:25
- Excel(エクセル) 複数のExcelブックのシート1の内容を1つのExcelブックにコピー貼り付けたいのでvbaコードを 7 2023/02/10 23:20
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) Excel VBA 指定セルに入力されているパスからシートをコピーして別のブックに転記するVBAのコ 2 2022/04/19 15:53
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBAでブックを非表示で開いて処...
-
エクセルの関数 ENTERを押...
-
エクセルを共有するとPCによっ...
-
エクセルで参照しているデータ...
-
Excelで複数ブックの同一セルに...
-
Excel(2010)のフィルターが保...
-
エクセルファイルを開かずにpdf...
-
Excelでブックの共有を掛けると...
-
エクセル2016です。「ブッ...
-
WorkBooksをオープンさせずにシ...
-
エクセルで50行ごとに区切った...
-
複数エクセルから特定シートの...
-
複数ファイルから特定シートの...
-
エクセルで開いていないbookの...
-
Excelファイルをダブルクリック...
-
VBA バックグラウンドで別ブッ...
-
ブックのピボットを別ブックに...
-
フォルダ内の複数ファイルから...
-
エクセルで「ディスクがいっぱ...
-
エクセル:特定のブックのみ、...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルの関数 ENTERを押...
-
VBAでブックを非表示で開いて処...
-
エクセルを共有するとPCによっ...
-
WorkBooksをオープンさせずにシ...
-
エクセルで参照しているデータ...
-
エクセルで「ディスクがいっぱ...
-
Excelでブックの共有を掛けると...
-
Excelで複数ブックの同一セルに...
-
Excel(2010)のフィルターが保...
-
エクセルで別ブックをバックグ...
-
エクセルにおける,「ブック」...
-
同じフォルダへのハイパーリン...
-
ブックのピボットを別ブックに...
-
エクセルファイルを開かずにpdf...
-
エクセル2016です。「ブッ...
-
ブックの保護ができないんです...
-
エクセルで50行ごとに区切った...
-
エクセルシートの一部を送りたい
-
フォルダ内の複数ファイルから...
-
エクセル 複数のブックを一度...
おすすめ情報