別シートにあるA列~V列の項目に検索値の入力した文字と一致したすべての行とその隣にある行を表に転記したい。

Question

###前提・実現したいこと
Sheet1とSheet2がありSheet１にはA列からV列までに項目（1番~２０番）項目の下には
A列（名前）B列（金額）は他のシートからSumif関数を使って集計しています。どちらも項目は一番としています。2列で一組です。そのデータをSheet2のA２に条件（1番）を入力すると表のA4には名前B4には金額が転記される。

###該当のソースコード
考え中
Dim sh1, sh2 As Worksheet
Dim row1, row2, maxrow1 As Long
Dim simei, kingaku As Variant
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
rangeshe1("A:V").Select

Comments

[coco_bauer]

どこで困っているのですか？　「項目は一番としています」というのは、どういう意味ですか？　"番"ではなく、Excelで標準的に使われている"行"や"列"を使って表現する事は出来ませんか。

[yochan1212]

すみませんでした。1番としているのは、文字列です。A列の項目が1番という文字列で、番号順に名前と、金額が入力されているデータがV列まで続いています。分かりますか？

[coco_bauer]

整理すると、「sheet1は名前と金額の対応表で、１０組(A:B列、C:D列、、、U:V列)の表が１つのシートに入っている。 Sheet2のA2セルに調べたい名前を入力しておいて、Sheet1の１０組の表の中に調べたい名前が入っていれば、それをA4セルに入れ、対応する金額をB4セルに入れるようなマクロを作りたい。」という事でしょうか？　Sheet1で名前が見つかれば、A4セルとA2セルの内容が同じになります。A4セルに名前を入れるのは何故？　また、見つからなかった時にA4,B4のセルには何を入れたいのですか？？？

[coco_bauer]

質問の続きです。Sheet1の表に、同じ名前が複数回含まれている事はありますか？その場合、どの金額をA4セルに入れれば良いのでしょうか？　Sheet1の１０組の列の長さ（行数）は同じですか、ばらばらですか？　どのようにすれば、各組の最後の行を判断できるのでしょう。

[yochan1212]

説明が下手ですみません。Sheet1で抽出したデータはSheet2の表があってA4に名前B4に金額が入ります。見つからなかった場合は空白のままで。Sheet1の名前は重複はありません。１０組の列の長さは最大で３０名位です。たびたびすみません。

[jawa]

シート１・２の開始前の状態、処理結果に期待する状態がイメージしにくいです。参考程度のボリュームでいいので、サンプルデータを提示していただけると回答がつきやすくなると思います。

[jawa]

あと質問文を読んでいて、文の切れ目がよくわからないところがあります。 投稿前に相手に伝わる内容か読み返すことと、改行や句読点を適度に入れて読みやすくするのも手かと思います。 例えば最初の一文は「Sheet1とSheet2があり～（中略）～Sumif関数を使って集計しています。」というところまで句読点なく書いてありますが、 自分はこれを（結構時間をかけて）「Sheet1とSheet2があります。Sheet１にはA列からV列まで項目（1番~２０番）が記載されています。項目の下には A列に名前、B列に金額が入っており、他のシートからSumif関数を使って集計しています。」と読み取りましたが、これでも意味がよくわかりません。あっていますか？

[coco_bauer]

Sheet2にある名前は１つだけ、それとも複数？ 「Sheet2のA２に条件（1番）を入力する」というのと、「Sheet2の表があってA4に名前B4に金額が入ります」との関係は？　　もう少し判り易い日本語を使ってください。

[yochan1212]

本当にすみません。Jawaさんの読み通りです。Sheet1には、名前と金額が複数あります。20行位です。Sheet2のA2に検索条件を入れると、Sheet1から一致した番号の名前と金額をSheet2に貼り付ける。必至に解り易く説明しようとしているのですが、初めて投稿したので、ご迷惑をかけています。

[coco_bauer]

そうすると、SHeet1で金額が入っているのはB列だけで、C列～V列に入っている値は、この質問では使わない（無視して良い）という事ですか？　それならSheet2のB４セルに関数式を記入するだけで解決できるのですが。

[yochan1212]

いえ、C列からV列まで交互に名前と金額が入ってます。共に名前＝金額で、検索で一致した番号の名前＆金額を抽出してSheet2に貼り付けたい。色々調べて、Find関数で出来ないかと・・

[coco_bauer]

vlookupで一組（例えばA-B列）を検索する。それを１０個用意して（１０個のセルに、vlookup関数の敷を入れて）、そのからエラーでないものを取り出せば出来そう。　括弧の入れ子の階層に上限があるから、たぶん１つの関数式で書くことは無理だと思います。

Answer 1

A列からV列までに１番～２０番が入っているとありますが、A列～V列は２２列あります。
さらにその下の行では、A列には「項目(１番)の名前」、B列には「項目(１番)の金額」が記載されており、２列で１組と記載されています。
そうなると２２列では１１組しか記載できませんが、１番～２０番はどのように入力されていますか？

ここらへんがイメージしにくいので、できればサンプルデータを記載していただきたいのです。

前提

(Sheet1)
・A列～V列の1行目には、2列１組で１番～２０番の範囲で値が入力されているが、存在しない番号もある。
・A列～V列の2行目には、１行目で記述した項目に対する名前と金額が入力されている。

Sheet1イメージ
   A   B   C   D   E   F ・・・ U     V   
==========================================
1| 1       2       4            20        
2| 一番100 二番200 四番400      二十番2000

(Sheet2)
・A2に入力された値をSheet1の1行目から検索する
・見つけた項目の項目名と金額をB4,C4に記載する

上記の前提でアドバイスさせていただきます。
また、この質問のキーワードはExcelになっていますが、考え中のVBAを記載していただいていますので、VBAでのアドバイスとさせていただきます。
（TeratailにはVBAというタグもあるので、そちらも追加すると見てくれる人が増えますよ）

やるべきこと

まずやるべきことをまとめると
①目的の項目を探す
②見つけた項目から名前・金額を取得する
③作成したVBAマクロの呼び出し方を決める
といったところが必要になると思います。

③について

③は根本になりますので先に考えておきましょう。
例えば
・シート上にマクロ起動用のボタンを配置する
⇒A2セルに値を入力したあと、ボタンをクリックすることでマクロが実行されてB4,C4に値が入力される。

・関数として使えるようなマクロを作成する。
⇒A2の値を引数として渡すことで名前または金額を返すマクロを作成する。Sheet2のB4,C4ではこの関数にA2セルを引数として記述しておく。

などが考えられます。

個人的には関数として作成したほうが便利そうかな？と思います。

①・②について

①の目的の項目を探す部分ですが、これはFind関数を利用します。
⇒参考

②Find関数で見つけたセルから列番号を取得すれば、それが名前取得列になります。
さらに+1すれば金額の取得列になります。

サンプル

以下は項目名を取得する関数のサンプルです。

Function GetName(vRng As Range) As String
    
    Dim RetVal As String        '関数戻り値
    
    Dim sh1, sh2 As Worksheet   '対象シート
    Dim SrchRange As Range      '検索範囲
    Dim SrchVal As String       '検索文字列
    Dim FindRng As Range        '検索結果
    Dim iColName As Integer     '名前取得列
    Dim iColYen As Integer      '金額取得列
    
    Set sh1 = Worksheets("Sheet1")
    'Set sh2 = Worksheets("Sheet2")     '関数で提供する場合、結果は関数を記載したセルになるのでSheet2の指定は不要になります。
    
    '検索文字列の設定（＝引数のセル値）
    SrchVal = vRng.Value
    
    '検索範囲
    Set SrchRange = sh1.Range("A1:V1")
    
    '検索
    Set FindRng = SrchRange.Find(What:=SrchVal, LookIn:=xlValues, LookAt:=xlWhole)
    If Not FindRng Is Nothing Then
        '見つかった場合
        iColName = FindRng.Column     '見つけたセルの列番号 を取得
        'iColYen = FindRng.Column + 1  '見つけたセルの列番号 + 1 を取得
        
        'Sheet1の２行目から名前を取得
        RetVal = sh1.Cells(2, iColName).Value
    Else
        '見つからなかった場合
        RetVal = ""
    End If
    
    GetName = RetVal
    
End Function

これでSheet2のB4セルに=GetName(A2)という式を記述すれば、項目に対する名前を取得できると思います。

完成品をまるまる提供するのが質問者さんのためになるとは思いませんが、今回は質問段階でだいぶ苦戦して時間を取られていましたので参考ソースとして提供させていただきました。
すこしイジれば金額の取得関数もできると思いますので、そちらはがんばってみてください。

Comments

[yochan1212]

伝え方が下手で大変ご迷惑をおかけしました。おかげさまでやりたいことが出来ました。本当に有難うございます。