ブック全体（シートは毎月増えます）を検索するには

質問

VBAを勉強中です。

Ｂ列に入力された日付から、
例えば2012年3月1日～31日を指定して該当があれば、
行全体をコピーして実行したページにある表に貼りつけるマクロを組みたいと思っています。
（さらに不必要な項目は削除したい！＆自動集計も）

検索は実行シートより前にあるシート全体で、
かつ、シートは毎月増えるので、具体的な指定はできません。

このような状況であれば、どんな関数を使用するのが良いですか？

検索する日付を指定するのにメッセージボックスを使用するなど、
手間があることは構いません。

どうとりかかるか、ヒントをいただければと思います。
よろしくお願いします。

Excel2007 /Windows Vistaを使用しています。

回答No.6

mitarashi · ベストアンサー

#1-3です。
次の様な状況でしょうか。
ブックは社員毎・年ごとに作成してあり、1201～1212までシートがある。
ある月のシートにおいて、日付を指定して、別の月にまたがるデータを抽出したい。
前年の（別のブックにまたがる）データも抽出する必要がある。
いずれにしても、見出しとデータの間に空欄があったり、セルを結合してあったり、フィルタオプションでの抽出には問題がありそうです。
hallo-2007さんのおっしゃる様に、データベースのレコードと同様の形式で、年度も、月もまたがる様に出来ればベストですが、少なくともVBAで取り扱い形にしないと、労多くして実りが少ないと思います。

なお、本サイト以外の場所に置いたデータへのリンクは見ないことを原則にしております。今回は乗りかかった船で見てしまいましたが。

お礼

すでに躓きました。

日付指定での検索が難しいとのことでしたので、
入金日欄の横に「入金日コード」として、先日考えた

「243」を入力するようにしてみました。

「DATA」というシートに複数シートの必要データをアウトプットするところまでうまくいきましたが、
次の課題は、今回は「243」のみ抽出するという方法です。

来月は「244」のみを検索したいということになります。

フィルタをかける際に、検索したい文字列を毎回自分で指定するところまでVBAで組むことは可能ですか？

補足

mitarashi様

こちらの知識不足により、原則を破ってまで見ていただいて本当に感謝しています。

おっしゃるように社員一人で１ブック、今のところは年毎に分けるのではなく、
ずっと同じものでいこうかと考えています。重くなったらそのときにリセットを。
（お恥ずかしいですが業種的に５年も６年もという人は少ないので・・・）

セルの結合（列）は不要なので修正したいと思います。

hallo-2007さんのところに書かせていただきましたが、
お二方のおかげで、作業イメージがだいぶ湧いてきました。

がんばって作業を進めてみます。
無料で相談にのっていただいていては申し訳ないほど、感謝しております。

回答No.8

mitarashi

#1です。期末で帰宅が遅く、ほったらかしになってしまい申し訳ありません。
ご提示いただいたコードは断片的で、どこで苦労されているのか分かりかねます。
一番の課題は複数シートを処理するところだと思うのですが。

>日付指定での検索が難しいとのことでしたので、
これはFindで検索する場合、書式等の影響を受けてしまう話であって、フィルタオプションで、平成．．．の書式のシリアル値を抽出する例を、以前の回答で行っております。

>フィルタをかける際に、検索したい文字列を毎回自分で指定するところまでVBAで組むことは可能ですか？
InputBoxでも使われれば良いと思いますが、検索条件を入れるセルの横にボタンを置いて実行するといった方法では駄目なのでしょうか？本筋に関係ないところなので、全体が動くようになってから、機能を充実させてはいかがかと思います。
http://officetanaka.net/excel/vba/tips/tips37.htm

お礼

ありがとうございます。

もう少し方法を整理してみようと思います。
気にかけていただき、本当にありがとうございました！！

回答No.7

mitarashi

#1ですが、どの様に進められているのかが分かりませんので、出来たところまでコードの開示をお願いします。

補足

ありがとうございます！
できているとは言えない段階ですが、
マクロの記録とインターネットで探したコードを元に作成しています。

K2に検索コードが入っている状況です。

Sub 支給額明細抽出()
'
' 支給額明細抽出 Macro
'

'
Sheets("DATA").Select
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Range("A3:H50").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("K1:K2"), _
CopyToRange:=ActiveSheet.Range("N7"), _
Unique:=False

End Sub

よろしくお願いします。

回答No.5

hallo-2007

＞いつか重くなってしまうかもしれませんが、
いえいえ、同じ量のデータも複数のシートに分けてもつ場合と
一枚のシートにある場合では、一枚のシートの方がファイルが小さくなります。
シートの数は、パソコンのメモリで限界が来ますし、処理も複雑に
シート１枚にデータを集める方が軽くなります。

＞ただ、データシートの一番下に追加するというところにワザが要りそうですね！
データのある最終の行番号は
Range("A1000").End(xlup).Row
といった方法で得られます。意味は
A1000から上へ移動して空白でなくなる行

Sheet(1)が集約データのシート
Sheet(2)が新規データの場合

Sub ボタン2_Click()
新規データの最後の行 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
MsgBox 新規データの最後の行
データの最後の行 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
MsgBox データの最後の行
Sheets(1).Rows(データの最後の行 + 1 & ":" & データの最後の行 + 新規データの最後の行).Value = Sheets(2).Rows("1:" & 新規データの最後の行).Value
End Sub

つまり、新規データの数を調べる
現在の集約データの数を調べる

集約データの最後の行の一つ下から、新規データの数分の行=新規データの1行目から最後の行まで

と式を実行すれば完了です。
手抜きして
Sub ボタン2_Click()
データの最後の行 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
MsgBox データの最後の行
Sheets(1).Range("A" & データの最後の行 + 1 & ":Z" & データの最後の行 + 1000).Value = Sheets(2).Range("A1:Z1000").Value
End Sub
新規データの範囲を十分大目にとっても大丈夫ですが

＞二重登録という問題も出てきます・
登録のマクロの最後に
Sheets("新規データ").Cells.ClearComments
でデータを削除してしまいます。
これで二重登録のミス防止を図ることが出来ます。

＞新規に追加されるページ以外にもデータを触るので、
＞アウトプットをやり直すことになります
何のことでしょうか、現在、複数にあるシートを一つにまとめる作業の事でしたら
１、とりあえず、手作業で行う
２、先に紹介したコードで
　　Sheets(1)は、一番手前にあるシートで、この場合、データを集約するシートを置いてください
　　Sheets(2)は、2番目にあるシートですので、
このコードで一度うまくいったら、次から、登録が終わったシートを削除してもう一度
実行する
なども少しは楽できるかもですね。

補足

ご回答ありがとうございます！

＞シート１枚にデータを集める方が軽くなります。

勉強になります。

いただいた式を挿入し、月ごとページから一覧に必要部分をコピーする作業は成功しました！

唯一、新規ページを削除することは本位ではありません。
というのも、月ごとのページで月ごとに変わる歩合率の計算をするようになっています。
その記録を見やすく残したいので、残念ながら月ごとシートは残す方向で検討したいと思います。

ただ、このアイデア大変ヒントにさせていただいて、
変更した月のシートのみを（恐らくページごとの手作業になると思いますが）、
一覧に写し、検索して必要データをコピーしたら、
こちらのページを削除する方向でできないか・・・と思いました。

扱っているものが高額なため、一人／月あたりの作業ページは多くて３ページ程度のはずなので。

少しずつ作業全景が見えてきました。がんばってみます。
本当にありがとうございます。

回答No.4

hallo-2007

＞どうとりかかるか、ヒントをいただければと思います。
私であれば、まず
＞シートは毎月増えるので・・・・
の部分を解消します。
しーとの構成を
データのシート　１枚　と
抽出結果のシートに出来ないかを検討します。
出来れば
データの入力を一枚のシートでひたすら下方向へ入力していく
毎月、データのシートがメールで来るなどであれば
新規データのシート　１枚
データシート　１枚
抽出結果のシート１枚で
１、新規のデータをデータシートの最終行以下に取り込む　VBA
２、抽出した内容を　抽出結果シートに取り込む　VBA
の２つ考えます。

１については、新規データを全て選択、データシートの最終行以下に
貼り付ける　流れで　コードを考えて見てください。

２については
http://www.eurus.dti.ne.jp/yoneyama/Excel/filter3.htm
あたりが参考になります。

データがシートで分散していると、何をするにも複雑になり、VBAの力を
借りる羽目になります。

お礼

hallo-2007さん

ありがとうございます！
新規に作成した部分を一枚のシートにアウトプットして、
それを検索、必要なもののみコピーですね！

いつか重くなってしまうかもしれませんが、
イメージが湧いてきました！

ただ、データシートの一番下に追加するというところにワザが要りそうですね！

研究してみます！

補足

早速試してみましたが、確かにデータを１枚にすることは可能でしたが、
ひとつだけ欠点がありました。

新規に追加されるページ以外にもデータを触るので、
アウトプットをやり直すことになります。
そうすると、二重登録という問題も出てきます・・・

全シートを一度にアウトプットさせるというマクロになりますかね・・・

回答No.3

mitarashi

#1-2です。
Findで全シート検索するコードを下記で回答していて、結構評判が良いです。
http://okwave.jp/qa/q5921087.html
そちらでは、ヒットしたセルを逐次Activateし中断して、引き続き検索できる様にしていますので、今回のご要求には冗長な部分が多いですが、全シートを処理するご参考にはなると思います。
また、少数の日付を検索するならFindでも良いのですが、一ヶ月間の範囲という事になると、平成24年3月*なんて検索は田中氏のサイトの通り制約があるので、フィルタオプション等の手段を用いる方が良いかもしれません。
extractシートに条件を設定し、Sheet3から抽出し、tempシートに一旦貼り付け（必要ならここで見出し行の削除、列の削除等行い）、extractシートに転送する例です。これを全シートに対して行えば良いと思います。

Sub test()
Dim myCriteria As Range, tempRange As Range, targetRange As Range

Set myCriteria = Worksheets("extract").Range("A1:B2")
Set targetRange = Worksheets("Sheet3").Range("B1").CurrentRegion
targetRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=myCriteria, CopyToRange:=Worksheets("temp").Range("A1"), Unique:=False
Set tempRange = Worksheets("temp").Range("A1").CurrentRegion
tempRange.Copy Worksheets("extract").Range("B4")
End Sub

実際のシートの状況が分かるようなご説明、シート名の情報も取り込みたいといった要求があれば明示願います。
また、不必要な項目は削除したいというのは、引っ張ってくるデータには不必要な列のデータは含めたくないという趣旨でしょうか。このあたりも具体化をお願いします。

このへんが明確にならないと、「どうとりかかるか」決まらないと思います。

補足

mitarashi様

本当にありがとうございます。
ここに画像を添付する方法がわからなかったので、
以下に全体像を載せてみました。

http://ameblo.jp/kahele/entry-11202163574.html

小さいので読めるかわかりませんが、少しでも全景をお伝えできればと思います。

上記ご回答に対し、一度別のシートに貼り出してから、
不要部分を削除するというアイデアに、なるほどと思いました。
確かにそれなら方法が見えそうです。

記入いただいた式は、試してみますね。

削除したい部分は、おっしゃるとおり
検索月に該当したら、列ごと抜き出したいのですが、
Ｆ列とＩ列を不要としたいという意味です。

日付での検索に難があれば、
たとえば「平成24年3月」は「243」という風にルールを決めて、
もう一列入力を増やして解決するのであれば、
日付検索にこだわっていません。

その場合は日付をいれたら
自動で数字を返してくれるところまで作成できたら最高ですが・・・

実際のシート名は、「1203」形式になっています。
DATE関数で自動で名前をつけるようにしてあります。

引き続きよろしくお願いします。

回答No.2

mitarashi

#1です。
参考URLはちゃんとお読みになりましたか？
「平成２４●月●日」形式との事ですが、

・文字列なのか、日付シリアル値で表示形式で和暦にしているのか。
・値で入力されているのか、式の結果として表示されているのか。
・表示形式を用いている場合、●月●日の●は、10未満の時は1桁か、それとも01といった表示にしているのか
・24が全角ですが、実際に文字列で全て全角で入っているのか

といった事が関わってきそうですが、いかがですか。

補足

申し訳ありません。
教えていただいたＵＲＬの内容を理解するのに時間がかかりそうなので、
とり急ぎ補足させていただきました。

自動で和歴表示をするようにしていますので、正しくは「平成24年3月1日」表記です。
3/1と入力したら勝手に変換します。

回答No.1

mitarashi

コードを組むことはさほど難しくないと思いますが、日付の検索は非常にやっかいなので、機能しないかもしれません。
下記URLをご覧になって、質問者様のブック内の日付データがどの様な形式で入っていて、表示形式がどうなっているか確認し、補足いただければ、回答が得られるかもしれません。
http://officetanaka.net/excel/vba/tips/tips131d.htm

補足

ご回答ありがとうございます！

今のところ、「平成２４●月●日」形式ですが、必要であれば変更も構いません。
やはり日付を検索するのは難関なのですね。

日付の他に何か記号を入力して検索とか、他の方法も検討する必要があるかもしれませんね。

貴重なヒントになりました。ありがとうございます！

さらにヒントを思いつきましたら、是非教えて下さい！