エクセルマクロで検索ボタンの作り方

質問

備品を管理するシステムを作ろうとしています。
下記項目のデータの入ったSheetが4つあります。(Sheet1～4)
Sheet5は検索シートにしたいと思います。
学校名
区分
機能別分類
品目類
取得年月日
品名
規格等
購入単価
購入先
廃棄年月日
保管場所
取得区分
それぞれ検索したいものを選んで、検索ボタンを押すとSheet1～Sheet4までのそれぞれ該当するものがピックアップされるようにしたいと思います。（例）品名の『ノート』を選択し、検索を押すとSheet1～4までの『ノート』が検索シートに全て表示される。
教えて下さい。

回答No.14

n-jun · ベストアンサー

もしかして、各シートを抽出した時に取得区分が空白になっていると言う事はないでしょうか？
その場合は、検索シートに転記されないですね。
ただ、取得区分に値があるのに転記されないとすると・・・？？？です。

以下は、取得区分に空白があっても対応できるよう変更しました。
差し替えてみて下さい。

Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim r As Range
Dim rr As Range, rs As Range
Dim v As Variant, vv As Variant
Dim i As Integer, j As Integer

Set WS = Worksheets("検索シート")
Set rr = WS.Range("B21")

vv = WS.Range("D2:D14").Value

For j = 1 To UBound(vv, 1)
If Not IsEmpty(vv(j, 1)) Then
GoTo nex:
End If
Next
End

nex:
WS.Range("A20").CurrentRegion.ClearContents
WS.Range("A20").Value = "学校名"
WS.Range("B20").Resize(, 12).Value = _
Worksheets("A").Range("A2:L2").Value

' 上と下の"A","B","C","D"は実際のシート名（学校名）に変更願います。

For Each v In Array("A", "B", "C", "D")

With Worksheets(v)

Set r = .Range(.[A2], .Cells(Rows.Count, "L").End(xlUp))
r.AutoFilter
r.AutoFilter Field:=j - 1, Criteria1:=vv(j, 1)
Set rs = .Range(.[A3], .Cells(Rows.Count, "A").End(xlUp)).Resize(, 12).SpecialCells(xlCellTypeVisible)

If rs.Item(1).Row <> 2 Then
rs.Copy rr
rr.Offset(, -1).Resize(rs.Cells.Count / 12).Value = v
Set rr = rr.Cells(rs.Rows.Count, 1).Offset(1)
End If
r.AutoFilter
End With

Application.CutCopyMode = False
Next
End Sub

ＰＳ．実際の検索には学校名と通し番号は使えないですけど。

補足

できました！！学校名と通し番号の検索はできなくてもそんなに問題なかったので大丈夫です。この検索は、検索値が国語なら、国語の語句のあるSheetは抽出されます。ちなみに。区分が「国語」,機能別分類が「教材」というように複数の条件で、それに当てはまるものを検索するときはまたマクロを変えなくてはいけませんか？
【検索シート】
　　C　　　　　　D
１
２学校名
３通し番号
４区分　　　　　　国語
５機能別分類　　　教材
６品目類　　　　　
７取得年月日
８品名
９規格等
10購入単価
11購入先
12廃棄年月日
13保管場所
14取得区分

回答No.5

n-jun

ANo.4
補足：
>（例）品名の『ノート』を選択し、検索を押すとSheet1～4までの
>『ノート』が検索シートに全て表示される。
『ノート』とはどこに表示されているのか？
検索ボタンを押すまでは、検索シートには何もデータがないはず。
では、抽出したい項目をどこで選ぶのでしょう？
（シート１～４に個々にボタンを作る？）

補足

分かりにくい説明ですみません。
Sheet1～4にはそれぞれの学校の所有する備品のデータが入っています。Sheet名は学校名にしています。A1には表題。A2:L2には通し番号,区分,機能別,分類,品目類,取得年月日,品名,規格等,購入単価,購入先,廃棄年月日,保管場所,取得区分の項目名を入れています。このシートに備品が入り次第どんどん入力していきます。
検索シートにはC2:14にシート名(学校名),通し番号,区分,機能別,分類,品目類,取得年月日,品名,規格等,購入単価,購入先,廃棄年月日,保管場所,取得区分の項目名を入れています。抽出したい項目はここで選びます。そして検索ボタンを押せば検索シートに該当するものを全て表示させたいです。表示させる欄は検索シートに設けています。

回答No.4

n-jun

私なら、４つのシート情報を蓄積シートに転記してから、蓄積シートで
【Excel(エクセル)基本講座：オートフィルタ(データ抽出)】を
実行します。

>あの備品を借りたいっていうときに検索をかけて所有する学校を調べる為です。
上記サイトでは、各項目に▼マークがあるのがわかるように、
どの項目からも抽出を実行できますよ。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter.htm

回答No.3

imogasi

事情があるのだろうが同類のシートで４つになっているのは、面倒なことです。
４回同じことを繰り返さないとならないでしょう。
とりあえず１シートについて
（１）検索の操作を行って、マクロの記録を採ること
（２）VBAでFindとFindNextの解説を探して、勉強すること.
WEBにもたくさん解説があるよ。
（２）は結構下記の理解が難しいよ。
・見つからなかったとき
・見つかったときのFind－＞FindNextへのつなぎ方
・どこでFindNextを打ち切るか

回答No.2

n-jun

ANo.1です。

補足：
検索シートに転記した後に何もしない（抽出が目的）の場合に適合します。
検索シートの具体的な使用目的は何でしょうか？

補足

この備品台帳の第１案では、Sheet1～4の全てのデータを蓄積シートに集め、そこからオートフィルタで検索するという形式でした。
第２案ではSheet1～4のデータの中から検索ボタンを押すと検索シートに検索結果がでるという形式を考えています。
検索シートには検索する項目をリストで選べる検索項目一覧表と、検索結果表示欄（該当がすべて表示される）、検索ボタンを置いています。
Sheet1～4というのは、備品を所有する学校4校の備品台帳です。検索シートの使用目的は、あの備品を借りたいっていうときに検索をかけて所有する学校を調べる為です。その検索結果は品名、学校名だけでなく入力してある全ての項目がでた方がいいです。

回答No.1

n-jun

エクセルでSheetからSheetへの飛ばし方
http://okwave.jp/qa3341952.html
こちらで１箇所にまとめてから

Excel(エクセル)基本講座：オートフィルタ(データ抽出)
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter.htm
この方法で抽出できます。
（マクロにこだわらなければですが）