Excelで全シートから検索し列で抽出する方法

このQ&Aのポイント
  • Excel2010で表をマクロで検索・抽出する方法について教えてください。
  • Sheet1に作成した検索ボタンを押すと検索フォームが表示されます。
  • 検索フォームのテキストボックスに入力した文字を基に、各シートから列を抽出します。
回答を見る
  • ベストアンサー

Excelで全シートから検索し列で抽出する方法

現在Excel2010で作成している表をマクロで検索・抽出したいのですが、 素人のため中々思うように出来なくて困っています。 Sheet2~Sheet4まで作成した表があります。 Sheet1には検索ボタンを作成し、検索ボタンを押すと検索フォーム(ユーザーフォーム)が出てきます。 検索フォームはTextBox1とCommandButton1があります。 ------ここまでは作成済みです-------- ・検索フォームのTextBox1に入力した文字(半角全角英数関係なく)をCommandButton1で検索を行う。 ・そしてその検索結果をSheet1の検索ボタン(セル A~B・1~2)を避けて表からコピーして抽出(複数ある場合は複数抽出)。 ・抽出内容は、検索でヒットしたセルの列全てを抽出(複数ある場合は複数の列が抽出される)。 ・同列に検索した文字が2つある場合も、抽出する列は1つ。 少しややこしいかもしれませんが、上記のようなマクロを作成したいのですが私では力が足りませんでした。 わかりやすく教えていただけると嬉しいです。

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

  • ベストアンサー
  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.2

>検索でヒットしたセルの列全てを抽出(複数ある場合は複数の列が抽出される)。 言わずもがなですが、「列」と「行」を言い間違えてはいませんね? 各シートごとに、検索とヒットしたセルをまとめて確保、一気にコピーする事でダブりを防止します。 private sub CommandButton1_Click()  dim i as long  dim h as range  dim res as range  dim target as range  dim c0 as string ’1枚目シートの事前掃除  with worksheets(1)  .range(.range("C1"), .cells(1, .columns.count)).entirecolumn.delete shift:=xlshifttoleft  end with  activeworkbook.save  set target = worksheets(1).range("C1")  for i = 2 to 4 ’2枚目から4枚目のシートを対象に   set res = nothing  ’検索開始   set h = worksheets(i).cells.find(what:=me.textbox1, lookin:=xlvalues, lookat:=xlwhole, matchcase:=false, matchbyte:=false)   if not h is nothing then ’もし有れば    c0 = h.address    set res = h.entirecolumn    do     set h = worksheets(i).cells.findnext(h)     set res = union(res, h.entirecolumn)    loop until h.address = c0 ’シート内の検索終了   ’コピーと次の貼り付け先の調査    res.copy target    set target = worksheets(1).cells.specialcells(xlcelltypelastcell).offset(0, 1).end(xlup)   end if  next i end sub #補足 あなたの各シートの「貼り付け先はどうなっているのか」「どんな内容がコピーされるのか」といった具体的に目に見える様子の情報が何もないので、「貼り付け先を掃除する」とか「どこに貼り付けたらいいのか」とかの情報の採取のため、大変回りくどい手管を弄しています。 たとえば「全シートとも1行目がきっちり埋まっている」といった、少しでも手掛かりになりそうな目に見える情報があれば、もうちょっと簡単なマクロにする事もできました。

himuro07
質問者

お礼

説明不足な点は補足させていただきました(質問まで付属してしまっていますが;) 解説付のマクロだったので、とてもわかりやすかったです。 動作も全く問題ありませんでした。 本当にありがとうございました。

himuro07
質問者

補足

説明不足で申し訳ありません。 ・列と行は言い間違えてません、大丈夫です。 ・貼り付け先のSheet1には検索ボタン以外の情報はありません。  Sheet1のC1から抽出内容が貼付される現在の形で問題ありま せん。 ・貼り付け先の掃除はすっかり忘れていて全く頭にありませんでした。  マクロに含めていただいて、ありがたかったです。 ・内容については具体的には言えませんが、文字・数字・画像が列に並んで表示されている形です。 (抽象的な言い方で大変申し訳ないです・・・) ・全シート、1行目はきっちりと埋まっています。 質問なのですが・・・ 画像も使用しているため、掃除の際に画像だけ残ってしまいます。 画像までオールクリアする方法もあるのでしょうか? また、現在はセル結合を使用していませんが、隣列と部分的にセルの結合を行った場合、両列同時に検索・抽出することは可能なのでしょうか?

その他の回答 (2)

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.3

>画像 それは想定外でしたが、あなたの言ってる「画像」と、シートの左上に置いてある「ボタン」などの区別がちゃんと付いているか心配します。 きちんと文字通り使い分けが理解できているのでしたら、掃除ブロックに activesheet.pictures.delete と一行追記します。 言わずもがなですがボタンにも画像を使っていたら当然一緒に消しちゃいますし、そもそも「画像」じゃなく実は別のモノが置かれてたら、当然これではダメです。 >部分的にセルの結合 説明があいまいですが、「部分的に」とは「A列やC列には結合セルは無いけど、D列とE列は上から下まで隣同士セル結合している」という意味で、「ある列の途中で結合しているセルもある」という事じゃないとします。 変更前: set res = union(res, h.entirecolumn) 変更後: set res = union(res, h.mergearea.entirecolumn) セル結合していないシートで変更後のマクロを使っても問題ありません。

himuro07
質問者

お礼

やりたいことが全て出来るようになりました。 本当にありがとうございました。

  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.1

こんばんは! 外しているかもしれませんが・・・ たたき台としてです。 Private Sub CommandButton1_Click() Dim k As Long, j As Long, cnt As Long Dim wS As Worksheet Dim str str = TextBox1.Value j = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column If j > 2 Then Range(Worksheets(1).Columns(3), Worksheets(1).Columns(j)).ClearContents End If cnt = 2 For k = 2 To Worksheets.Count Set wS = Worksheets(k) For j = 1 To wS.Cells(1, Columns.Count).End(xlToLeft).Column If TextBox1 = "" Then MsgBox "検索データを入力してください。", vbExclamation TextBox1.SetFocus Exit Sub Else If WorksheetFunction.CountIf(wS.Columns(j), str) Then cnt = cnt + 1 wS.Columns(j).Copy Worksheets(1).Columns(cnt) End If End If Next j Next k End Sub ※ 条件としてSheet2以降の表は1行目に項目など何らかのデータが入っているという前提です。 (1行目で各Sheetの最終列を取得しているため) こんな感じではどうでしょうか?m(_ _)m

himuro07
質問者

お礼

説明不足な点も多く申し訳ありません。 検索・抽出と望む内容で実行することが出来ました。 ありがとうございました。

関連するQ&A

  • ▲▲検索欄を設置し該当する列を抽出する▲▲

    いつもお世話になっております。 マクロを学び始め、調べに調べたのですが、行き当たりました。 ご教授頂ければ幸いです。 タイトル通りで御座います。sheet1にフォームを作成し それをsheet2に引っ張ることはできました。 続いて検索をさせたいのですが、それがうまくいきません。 写真を添付しているのですが、B2に検索したい文字を入力 ※B2についてはセルでは無理かと思いますので、テキストボックスになるかと思います。 D2~D4のボタンを押すことで特定の部分から検索を行い 列を抽出しB10配下に該当するものを抽出したいのですが、全くうまくいきません。 ご教授頂ければ幸いです。

  • エクセルの検索・抽出マクロについて教えて下さい。

    急遽、会社のマクロ使用可のパソコン(ヴィスタ)で、 検索・抽出のデータ作業をすることになりました。 オートフィルタの貼り付けでは、時間がかかりすぎるということで、 職場の少ない知恵を出し合って考えていますが、なかなか上手くいかず、こちらにも お尋ねさせていただきます。 シート1に、3列(番号、文字、英字)の 『参照データ』があります。 シート2の 『検索する番号』 に番号を入れ、 『検索開始』ボタンを押すと、 シート1の『参照データ』から抽出されたデータが、 シート2に 『検索後のデータ』として表になって出てくるような関数(の場合はボタンなし)か、マクロをつくりたいのですが・・ 更には シート2の『検索する番号』を、番号ではなく 表の真ん中の列の『文字』でも検索できるように(番号でも文字でも、どちらでも検索できるように・・)は出来ないでしょうか・・・ ご回答を、心から お待ちしております! よろしく お願いします (u_u)

  • エクセル マクロ 文字列の検索と抽出

    エクセル2010を使っています。 画像の様に、【Sheet3】のA列~CQ列に 空白・エラー・数値・文字 の入った表があります。 そのL列には、画像の様な形で様々な地域の名前が入っています。 やりたいのは、L列に 検索対象文字が複数、例えば 福岡 か 大阪 が入っていたら、その表ごと抽出し、【Sheet1】の上から順に枠線ごと貼り付けて行きたいと思います。 この時、両方一致ではなく、検索対象の文字列が一つでも含まれていたら抜き出したいと思います。 その時に 【Sheet3】から抽出した部分は削除し、上に詰めて行きたいと思います。 ちなみに検索対象が、福岡・大阪・名古屋等、増える場合もありますので、増えたとき対応が出来る形が理想です。 ちなみにL列の文字と文字の間は ・ だったり / だったりします。 詳しい方、よろしくお願い致します。

  • エクセルのフォームに検索結果を表示する方法について

    エクセルのフォーム上に商品名を入力して、別シートに入っているデータを検索し、該当するデータを上記フォーム上に表示させたいと考えています。 【エクセルのフォームの概要】 (1)フォームはuserform1に作成してあります。商品名を入力→txtbox1 (2)「商品一覧表」というシートに商品一覧表が作成されていて、A1~J1000までデータが入っています。 (3)検索に引っかかるデータはその一覧表の左から2番目(商品名)の列です。 (4)該当するセルがある行の左から3番目のデータをフォームのtextbox2に表示させたいと考えています。 下記のように考えていますが、どうもうまくいきません。ご指導の程宜しくお願い致します。(つい最近マクロを始めたばかりなので、とんでもない構文なのかもしれませんが、自分ではどこがどうダメなのかわかりません。) Private Sub CommandButton1_Click() Dim RangeA As Range Set RangeA = Worksheets("商品一覧表").Range("A1:J1000") UserForm1.TextBox2.Text = Application.WorksheetFunction.VLookup(TextBox1.Value, RangeA, 3, False) End Sub

  • Excell(2003) VBA UserForm上のSheetの参照方法は

    Excell2003にてuserForm1にコントロールとしてスプレッドシートを載せています。 このuserForm1には、別にテキストボックス(textbox1)とコマンドボタンが二つ(commandButton1とCommandButton2)があります。 commandButton1がクリックされたとき、textbox1に入力された値をチェックしたのち、Me(userForm)上のSheet1の適当なセル(たとえばA2)にその値を入力したい。  また、CommandButton2がクリックされたときは、Sheet2のA2に入力したい。その場合Sheet2を表示する動作も必要です。  この場合 Q:本体のSheetではなく、フォーム上のSheetを指定する方法 Q:複数のSheetからなるフォーム上のspledSheetのいずれかを表示する方法  はどのようにしたら良いのでしょう。

  • エクセル:文字列間の行を抽出

    エクセルファイルで1つのシートに複数の表がひたすら縦に並んでいます。それぞれの表が別々のシートに記載されるようにしたいと思っています。 どの表も基本的な書式は同じなので列数は同じなのですが、行数は表ごとに異なります。 また各表の右下(C列)にTotalという文字列が必ずあります。 文字列Totalがある行の直下の行から次のTotalのある行まで抽出して、シートを作成できれば良いのだと思うのですが、どうすれば可能になるのか分かりません。 どなたか教えていただけ無いでしょうか。

  • Excelのユーザーフォームに検索機能をつけたい

    書籍やインターネットををみながらユーザーフォームを作成し、前後への移動、 データ修正、新規作成、削除を行うことは無事にできるようになりました。 しかし、このままでは閲覧、修正がメインとなってしまい、 検索ができない為何とかできないものかと考えています。 A列 B列 C列    D列  E列 1  A  タイトル1 本文1 更新日 2  B  タイトル2 本部2 更新日 3  A  タイトル3 本部3 更新日 上記のようにシートに追記されていきます。 H2がデータ最終行となります。H1がユーザーフォームに表示されてる行です。 ユーザーフォームの情報は以下の通りです。 TextBox4・・・A列 ComboBox1・・・B列 TextBox1・・・C列 TextBox2・・・D列 TextBox3・・・E列 CommandButton1・・・前に移動 CommnadButton2・・・後ろに移動 CommandButton3・・・データ修正 ToggleButton1・・・新規作成 CommandButton5・・・削除 ToggleButton2・・・検索(を予定) ユーザーフォームを起動するとSheetは非常時となります。 その中で各項目の入力や修正を行っております。 「検索」ボタンを取り付けてありますが、機能はしていません。 現在、希望している動作としまして、以下の通りです。 1.可能であればもう1つの検索ユーザーフォームを表示  検索欄は1つ、ボタンも1つ。 2.入力した検索キーワードが含まれているデータを  C列、D列から検索する。 3.最初のユーザーフォームに戻り、検索結果を確認が可能。

  • 検索後、削除や抽出するマクロ

    エクセルのシート1、A列の1~1500のセルにテキストが入力されており ある特定のテキストを含むセルを全て シート2のA列に抽出したいのですが、検索してみましたがうまくできません。 作業の流れとしては シート1のA列を範囲指定し、編集-検索、検索する文字列を入力し、すべてを検索をクリック 検索結果をコピーし、シート2のA列に貼り付ける もうひとつはシート1、A列の1~1500のセルにテキストが入力されており テキスト1またはテキスト2を含むセルを全て削除するというマクロを作成したいです。 ご指導のほどよろしくお願いいたします。

  • マクロの検索と抽出について

    いつもこのサイトの皆様にはお世話になっておりますm(_ _)m この度、マクロの検索と抽出について教えていただきたいのですが、 シート1(シート名:住所録)のB9からE30に取引会社の住所録を入力しております。 B列は1~30までの数字、C列には会社名、D列には住所、E列には郵便番号をそれぞれ入力しております。 会社名をキーワードに検索をかけて、検索結果をシート2(シート名:抽出結果)に出力されるようなマクロを作りました。下記を参考にしていただきたいのですが、質問としましては、検索を何回か繰り返し行いたく、そしてその結果を抽出結果のシートに反映させる際に、前回の出力されたセルの下に反映されるようにしたいのです。 質問にまとまりがなくて伝わりづらかったとは思いますが どなたか教えていただけないでしょうか。 よろしくお願いいたします。 Private Sub CommandButton1_Click() '#### 最初に、テキストボックスの条件を住所録シートに転記しておく。 If OptionButton1 = True Then '部分一致にチェックが入っていたら '部分一致検索は、検索条件を「*」で囲む Worksheets("住所録").Cells(4, "C").Value = "*" & TextBox1.Text & "*" ElseIf OptionButton2 = True Then '完全一致にチェックが入っている場合 '完全一致検索は、検索条件の先頭に「'=」をつける Worksheets("住所録").Cells(4, "C").Value = "'=" & TextBox1.Text End If '####  検索を実行 Sheets("抽出結果").Select '抽出結果を表示するシートを選択しておく。 Cells.Select '前回の抽出結果を消しておく Selection.Clear Range("A2").Select Sheets("住所録").Range("B9:E109").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("住所録").Range("C3:C4"), _ CopyToRange:=Sheets("抽出結果").Range("A1"), _ Unique:=False End Sub Private Sub CommandButton2_Click() On Error Resume Next Worksheets("住所録").ShowAllData End Sub Private Sub CommandButton3_Click() TextBox1.Text = "" End Sub Private Sub UserForm_Initialize() OptionButton1 = True 'あいまいにチェックを入れておく End Sub

  • VBAで列の検索抽出について

    EXCEL2003のマクロについて質問です。InStr構文でうまくできませんでした。 SHEET1のA列(A1~A9000)に文章データが9000あり、SHEET2A列(A1~A500)に単語が500ある時、SHEET2のA列にある500の単語の中で1つでもSHEET1のA列の文章の中に含まれているセルがある場合を別シート(SHEET3)に抽出したい場合はどのようなマクロを組めばいいのでしょうか? 宜しくおねがいします。

専門家に質問してみよう