[[20201103163059]]　『エクセル　vba　別ブックから参照　』（よねこ）　ページの最後に飛ぶ

『エクセル　vba　別ブックから参照　』（よねこ）

質問です。
転記させたいシート(シート名:検索表）

 　　A 　B 　　C
 1　11AA
 2   D   Z     H    11  AA      
 3   C   X     G
 4   E   R     J
.
.
.
260
A1セルには検索値が入る(例:11AA)
抽出先シート名(シート名は毎回違う）
     A   B     C....AA　AB 
 1   D   Z     H    11  AA      
 2   C   X     G
 3   E   R     J
 .
 .
 .
 260 H         K

AA1には11 AB1にはABが入っているとする。ここは毎回は変わりますにで今回は例えばの話でお願いします。
転記させたいシートの検索ボタンを押すと
ファイルを開く画面
OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")のマクロを入れて
今回開きたいファイルを開きます。
そうると転記させたいシート(シート名:検索表）の検索値
11AAを検索値として、抽出先シートからそれにあてはまるセルを抽出させたいです。抽出範囲(A1からC260)の値を抽出したい。
VLooKUPでしたいです。
そのような場合どのようにマクロを組めばよいでしょうか？
どうか教えてください。
ちなみに検索値が複雑で抽出先シートには２つのセルに分かれているので(11とAA)どちらもある場合のみ検索をかけ抽出する仕組みにしたいです。
説明不足かもしれませんがお願いします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 今少し条件があいまいに見えるのですが、

 たとえば抽出先シートで
 11   AA    35
 AA   11    BB
 CC   11    AA
 というデータがあったら、このまま「検索表」に追記すればよいのでしょうか？

 その場合
 >VLooKUPでしたいです。
 の意味が分からないのですが。
（QS） 2020/11/03(火) 17:42

（QS）さん
お返事ありがとうございます。
たとえば抽出先シートで

 11   AA    35
 AA   11    BB
 CC   11    AA
 というデータがあったら、このまま「検索表」に追記すればよいのでしょうか？
ですが、
    　A   B     C 
 1   11  AA     35    
 2   AA  11     BB
 3   CC  11     AA
のように検索表に追記したいです。

検索→抽出(転記)はVLookUPだと思っていました。
間違いなら勉強不足です。
すみません。
検索値がある以上Vlookupだと思っていました。
（よねこ） 2020/11/03(火) 17:51

 VLOOKUP というのは Virtical Lookup（縦方向値抽出）で対の機能が HLOOKUP（Horizontal Lookup）。
 なので、EXCEL で VLOOKUP というとほとんどの方はこの関数と解釈するかと思います。

 今回の処理を表す関数はないので、素直に
 「対象シートのA:C列の同一行に 「検索表」シートのAA1セルと AB1 セルにある値を持つ行を抽出し、
　検索表の A:C列に追記していきたい。」
 のように説明したらよいかと思います。

 やりたいことは、上記でよいでしょうか。
 コードは後程。 
（QS） 2020/11/03(火) 18:09

（QS）さん
お返事ありがとうございます。

やりたいことはあっています。
すみません。

よろしくお願いします。

（よねこ） 2020/11/03(火) 18:15

 サンプルまで。

 Sub Sample()
    Dim dstWS As Worksheet
    Set dstWS = ThisWorkbook.Worksheets("検索表")

    Dim dstRow
    dstRow = dstWS.Cells(Rows.Count, "A").End(xlUp).Row + 1  '// A列で最終行を判定

    Dim OpenFileName
    OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")  '// 選択ファイルは開いていない前提

    Dim srcWS As Worksheet
    Set srcWS = Workbooks.Open(OpenFileName).Worksheets(1)  '// 先頭シートを仮定

    Dim srcLastRow
    srcLastRow = srcWS.Cells(Rows.Count, "A").End(xlUp).Row

    Dim searchVal1
    searchVal1 = dstWS.Range("AA1").Value

    Dim searchVal2
    searchVal2 = dstWS.Range("AB1").Value

    Dim r
    For r = 1 To srcLastRow
        If WorksheetFunction.CountIf(srcWS.Cells(r, "A").Resize(1, 3), searchVal1) > 0 _
        And WorksheetFunction.CountIf(srcWS.Cells(r, "A").Resize(1, 3), searchVal2) > 0 Then
            srcWS.Cells(r, "A").Resize(1, 3).Copy dstWS.Cells(dstRow, "A").Resize(1, 3)
            dstRow = dstRow + 1
        End If
    Next
 End Sub

（QS） 2020/11/03(火) 19:36

（QS）さん
お返事ありがとうございます。
コードありがとうございます。
Sub Sample()

    Dim dstWS As Worksheet
    Set dstWS = ThisWorkbook.Worksheets("検索表")
    Dim dstRow
    dstRow = dstWS.Cells(Rows.Count, "A").End(xlUp).Row + 1  '// A列で最終行を判定
    Dim OpenFileName
    OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")  '// 選択ファイルは開いていない前提
    Dim srcWS As Worksheet
    Set srcWS = Workbooks.Open(OpenFileName).Worksheets(1)  '// 先頭シートを仮定
    Dim srcLastRow
    srcLastRow = srcWS.Cells(Rows.Count, "A").End(xlUp).Row
    Dim searchVal1
    searchVal1 = dstWS.Range("AA1").Value
    Dim searchVal2
    searchVal2 = dstWS.Range("AB1").Value
    Dim r
    For r = 1 To srcLastRow
        If WorksheetFunction.CountIf(srcWS.Cells(r, "A").Resize(1, 3), searchVal1) > 0 _
        And WorksheetFunction.CountIf(srcWS.Cells(r, "A").Resize(1, 3), searchVal2) > 0 Then
            srcWS.Cells(r, "A").Resize(1, 3).Copy dstWS.Cells(dstRow, "A").Resize(1, 3)
            dstRow = dstRow + 1
        End If
    Next
 End Sub
のコードの中で検索範囲はどこのことを言っているのでしょうか？
searchVal1 = dstWS.Range("AA1").Value
    Dim searchVal2
    searchVal2 = dstWS.Range("AB1").Value
が検索値というのは理解したのですが、
一番初めに説明した
　　A 　B 　　C
 1　11AA
 2   D   Z     H    11  AA      
 3   C   X     G
 4   E   R     J
.
.
.
260
A1セルには検索値が入る(例:11AA)
は今回のコードでは反映せれていないでしょうか？

（よねこ） 2020/11/03(火) 20:01

 A1 は何にもしていませんが、あらかじめ式で =AA1&AB1 として置いてはダメでしょうか。
 検索は 「AA11」という一つにまとめた値で検索を行うのでしょうか？

 そうではないということを確認するために、先の質問をさせていただいたのですが。
 処理結果が異なるのであれば、やりたいことが今一つ理解できていませんが、
 そこは適宜変更いただいて、わからない部分を記載いただくか、他の方の回答をお待ちください。
（QS） 2020/11/03(火) 20:10

（QS）さん
すみません。
自分なりにやりた事をまとめてみました。
ご確認ください。
検索は「AA11」という一つにまとめた値で検索を行うのでしょうか？
→その通りです。
検索値が複雑で抽出先シートには２つのセルに分かれているので(11とAA)どちらもある場合のみ検索をかけ抽出する仕組みにしたいです。
抽出さきシートにはAA11というセルはなく2つセルにAA,11という文字が入っています。
VLOOKUP関数は
検査値を検索範囲を検索し、一致したセルの何番目のセルを返す関数と認識しているのでしょうがあっっていますでしょうか？
今回の場合だと
転記させたいシート(検索表)の
A1セルに検索値が入っていて
検索範囲は別ファイルの例：A1からC260の範囲で
抽出したいセルは検索と一致した場所から例：左に10,11,12とします。
別ファイルに検査値は多数あり、すべて抽出したいです。
以前にも書きましたが、検索値が複雑で抽出先シートには２つのセルに分かれているので(11とAA)どちらもある場合のみ検索をかけ抽出する仕組みにしたいです
理解できますでしょうか？
（よねこ） 2020/11/03(火) 20:16

 わたしには、
　　 >検索は 「AA11」という一つにまとめた値で検索を行うのでしょうか？
　　 >→その通りです。
 と
 　　>検索値が複雑で抽出先シートには２つのセルに分かれているので(11とAA)どちらもある場合のみ
 　　>検索をかけ抽出する仕組みにしたいです。
 は矛盾している説明に聞こえます。

 　　>抽出先シートにはAA11というセルはなく2つセルにAA,11という文字が入っています。
 であれば、
 　　>検索は 「AA11」という一つにまとめた値で検索を行うのでしょうか？
 ではない。ではないでしょうか。

 VLOOKUP は 第一引数を検索する範囲は第二引数で指定した範囲(表）の「先頭列」です。
 これを範囲といっているのであれば正しいですが、説明で検索する範囲が A:C列となっている
 ため、これが VLOOKUP と言われてもピンと来ていません。

 >抽出したいセルは検索と一致した場所から例：左に10,11,12とします。
 はこれまでの説明にあったでしょうか。
 急に出てきた新しい条件のように感じましたが、どこの説明になるでしょうか。

 >理解できますでしょうか？
 すみません、理解できません・・・onz。
（QS） 2020/11/03(火) 20:56

 こんばんは！
横からお邪魔します。
察するに検索範囲は、シート全体かもしくはD列から最終列ぐらいを指すんでしょうかね？？？
 >AA1には11 AB1にはABが入っているとする。ここは毎回は変わりますにで今回は例えばの話でお願いします。
で、抽出したいのがその行のA列B列C列の値なのですね？？？

 検索値と検索範囲がうまく伝わらないですね
それはさておきいくら何でも何か範囲を特定する仕様はあると思いますが、回答者からはトピ主さんのレイアウトは
見えませんので想像で書くと

 D1からXAA1の間に　A1とB1の値があったら、、っていうコードですけど、、こういうことではなのですか？
Option Explicit
Sub nn()
Dim myrng As Range
Set myrng = Range("d" & 1, Range("xaa" & 1))
If (Application.CountIf(myrng, Range("a1")) > 0) * (Application.CountIf(myrng, Range("b1")) > 0) Then

 End If
End Sub
（SoulMan） 2020/11/03(火) 21:00

（QS）さん
すみません。
説明が足りないみたいで、
矛盾しています。
本来は検索値が複雑で抽出先シートには２つのセルに分かれているので(11とAA)どちらもある場合のみ

 　　>検索をかけ抽出する仕組みにしたいです
が本来の姿です。
ちょっと書き方が間違っていたので修正します。
転記させたいシート(検索表)の
A1セルに検索値が入っていて
検索範囲は別ファイルの例：A1からAA260の範囲で
抽出したいセルは検索と一致した場所から例：左に10,11,12とします。
別ファイルに検査値は多数あり、すべて抽出したいです。

左に10,11,12はある特定の場所だけを抽出したいです。
全部の行じゃなくて。
すみませんでした。

（よねこ） 2020/11/03(火) 21:13

（SoulMan)さん
お返事ありがとうございます。
転記させたいシートの検索ボタンを押すと
ファイルを開く画面
OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")のマクロを入れて
今回開きたいファイルを開きます。
そうると転記させたいシート(シート名:検索表）の検索値A1セルの
11AAを検索値として、

検索範囲は別ファイルの例：A1からAA260の範囲で
抽出したいセルは検索と一致した場所から例：左に10,11,12とします。
別ファイルに検査値は多数あり、すべて抽出したいです。
検索値が複雑で抽出先シートには２つのセルに分かれているので(11とAA)どちらもある場合のみ

 　　>検索をかけ抽出する仕組みにしたいです。
すみませ。

（よねこ） 2020/11/03(火) 21:19

 >検索範囲は別ファイルの例：A1からAA260の範囲で
 >抽出したいセルは検索と一致した場所から例：左に10,11,12とします。
 >別ファイルに検査値は多数あり、すべて抽出したいです。
 >検索値が複雑で抽出先シートには２つのセルに分かれているので(11とAA)どちらもある場合のみ

 お手伝い程度しかできないのですが、、取り合えず検索範囲が分かったのはよかったですね。
でも、この程度のことは最初から提示しておかないと互いが無駄な時間を過ごします。

 次に、検索値が二つありますよね？11　と　AA　
で、一致した場所からとなるとどっちですか？それぞれですか？？？
A1の左に10も11もありませんよね？？？
全て抽出したいということは複数あるのですね？？？
その場合は対をどうやって判断するのですか？？？

 何か条件が曖昧過ぎてコードが書けないんじゃないんですか？
もう少し小さなレイアウトでいいですから表できちんと例題を示されて
それをご自身で応用するぐらいの気構えがないと厳しいと思いますよ？

 コードが書けない人の特徴はコードを書く以前の問題がクリアされていないからだと思うのです。私見です。
もう少しご自身で整理されてみてはいかがですか？
（SoulMan） 2020/11/03(火) 21:39

（SoulMan）さん
お返事ありがとうございます。
ご指摘ありがとうございます。
　　A 　B 　　C

 1　11AA
 2   D   Z     H         
 3   C   X     G
 4   E   R     J
.
.
.
260　H         K
A1セルには検索値が入る(例:11AA)
抽出先シート名(シート名は毎回違う）
     A   B     C....AA　AB 
 1   D   Z     H    11  AA      
 2   C   X     G　　11  AA
 3   E   R     J    11  AA
 .　　　　　　　　　
 .
 .
 260 H         K
簡単な表です.
次に、検索値が二つありますよね？11　と　AA　
で、一致した場所 からとなるとどっちですか？それぞれですか？？？
A1の左に10も11もありませんよね？？？
全て抽出したいということは複数あるのですね？？？
その場合は対をどうやって判断するのですか？？？
これに関しては、考えがまちがっていました。
やりたいのは,抽出先シートに11,AAの二つのセルがありまして、そのセルを含むすべての行のA,B,C列の値を抽出したいです
すみません。
（よねこ） 2020/11/03(火) 21:55

 もうお布団の中ですけど
それでしたら範囲は違いますが
前の投稿で書いてますよ？
そこから応用されたらどうでしょうか？
そんなに難しい話じゃないので
頑張れば出来るとと思います
では、おやすみなさい💤
（SoulMan） 2020/11/03(火) 22:12

今更ですが、私の説明不足のせいでご迷惑おかけ誠にすみません。
もう一度ご教授いただきたく、お願いに参りました。

転記させたいシート　シート名検索表
セルの配置は以下になります。
D2セルにはユニット名　→ファイルによっては入るものと入らないものがある　入らない場合は空白です。
D3セルにはマウント表、マウント裏、マウント→どれかが必ず入る
D4セルには＝D2&D3の数式が入る

抽出先シート　→ファイル名は毎回変わる　
検索範囲はB7からAH290

ここからは流れを説明します。
転記させたいシートの検索ボタンを押す
ファイルを開くダイアログが表示されて開きたいファイルを開く
転記させたいシートのD4セルを検索値として、
抽出先シートのB7からAH290の範囲で検索し、検索値と一致した行のＢ,C,D,E列の値を
全て転記させたいシートのC,D,E,F列からに転記する
という流れになります。
この流れをVBAで行うにはどうしたら良いでしょうか？
ご教授いただきたく思います。

（よねこ） 2020/11/05(木) 12:20

すいません。補足です。
転記させたいシートに転記する際は
C,D,E,F列の7行目から転記させたいです。
７行目から上は文字が入っているためです。
7行目から下は空白です。
（よねこ） 2020/11/05(木) 12:31

すみません。違うサイト、解決できるかもしれないのて、質問は終了させてもらいます。
最後まで説明不足ですみません。
ご協力ありがとうございました。
（よねこ） 2020/11/05(木) 20:01

コメント返信：

[ 一覧(最新更新順) ]

[[20201103163059]] 『エクセル vba 別ブックから参照 』（よねこ） ページの最後に飛ぶ

[[20201103163059]]　『エクセル　vba　別ブックから参照　』（よねこ）　ページの最後に飛ぶ