EXCEL VBAについて質問です

質問

最近EXCELのマクロを組む勉強を始めました。
幾つかわからないことがあるので、教えてください。

お店の在庫と売上を管理するシートを作成しています。
その管理表では年度ごとにシートを分けています。
調べたいのは、今回注文を受けた会社から過去に注文を受けたことがあるか、受けたことがあれば何年度の何月に受けたか、という内容です。
具体的には「注文者」の名前で検索して、発注日をリストで表示させたいと考えています。

例）
Ａ株式会社から商品の注文を受けた
2015年度～2019年の５枚のシートで「Ａ株式会社」を検索し、発注された日を確認したい。

エクセルにもともと備わっている検索機能を使ってもよいのですが、一番知りたいのは「過去にいつ発注されたか」です。本来の検索機能では発注日をリスト表示できません。

各シートのフォーマットはそろっていて、会社名の左隣に発注日が入力されています。※会社名が入力されているのは各シートのＣ列です。

まずは特定のシートで計算してリストに表示させるマクロを組み、
それがうまくいったらワークシートのインデックス番号を変数としてFor文でループさせてみよう…と考えたのですが、
そもそも特定のシートでもうまくいきませんでした。（列を範囲指定して検索しているはずなのに、そのシート上すべてで検索されてしまう。たとえばＥ列＝備考欄にＡ株式会社という名前が入っていると、そのセルもリストに表示されてしまう。）

これ以上は自分だけで考えていてもうまい方法が思いつかないので、お知恵をお貸しいただけると幸いです。

（似たようなマクロ関連の質問をいくつか投稿しておりますが、初歩的な質問ばかりで申し訳ありません）

Sub 検索()

Dim FoundCell As Variant
Dim FirstCell As Variant
Dim mRange As Range
Dim keyword As Variant

Set mRange = Worksheets("２０１９年度").Range("C1:C100")

keyword = Application.InputBox("調べたい会社名を入力してください")

Set FoundCell = mRange.Find(What:=keyword, SearchOrder:=xlByRows)

If FoundCell Is Nothing Then
MsgBox "過去に発注を受けた履歴がありません"
Exit Sub
Else
Set FirstCell = FoundCell
UserForm1.ListBox1.AddItem FoundCell.Address & vbTab & FoundCell.Value & vbTab & FoundCell.Offset(0, 1).Value
End If

Do
Set FoundCell = Cells.FindNext(FoundCell)
If FoundCell.Address = FirstCell.Address Then
Exit Do
Else
UserForm1.ListBox1.AddItem FoundCell.Offset(0, -1).Value & FoundCell.Address & vbTab & FoundCell.Value
End If
Loop
UserForm1.Show vbModeless
End Sub

回答No.1

kkkkkm · ベストアンサー

> Set FoundCell = Cells.FindNext(FoundCell)

Set FoundCell = mRange.FindNext(FoundCell)
ではないでしょうか。
Cellsはすべてのセルになります。

補足

＞kkkkkmさん
ご指摘の部分を修正したらすぐに解決しました…きちんと理解していないので基礎的なところを見落としてしまうのですね…ありがとうございました！
ちなみにもしお分かりでしたらお教えいただきたいのですが、これと同じ検索を複数シートで同時に行う場合は、ワークシートのインデックス番号を変数iとして、for文で繰り返し計算させれば実行できるのでしょうか？

回答No.3

imogasi

何を質問しているのかな？
文章で、聞きたいことを、箇条書きしたら。
希望は掲示したコードの添削ですか？
質問文章は長いが、主題がぼやけている。
ーー
小生の経験から、Ｆｉｎｄ関連は、学習の苦労が多いテーマだが、そのボヤキと主題が混じって、聞きたいことが、わかりにくい。
Find、FindNextはRangeセル範囲に関しての検索なので、＞「＝備考欄の文字該当分も含まれる」というのはどうかな。
ーー
小生が、むつかしい（簡単な方法がない）と思うのは、年度ごとに（データ）シート（ブック？）を分けている点です。しかし分けるのも自然だとおもうが。
ーー
それも繰り返し処理でも良いとして、やるなら、（For　Each　　・・Next)泥臭いが簡単です。
ただ処理時間がかかるような気がします。泥臭いので個人の好みではない点だけです。
Ｆｉｎｄメソッド以外に良い方法がない。
１つのシートを検索する（条件該当分はすべて拾う）のコード例は、「VBA　Find」でWEB照会すれば、たくさん記事がある。
　ややこしいとは思うが、まあ慣れでしょう。
アクセスにエクセルにある過去データをエクスポートしておいて、Ｓｅｌｅｃｔ文でやれば速いかな。
ーー
参考・確認
WEB記事のコードを使って
下記で、範囲外セル（Range("A1:C8")外）に、「土屋」を入れて検索すると、出てこなかったが。
Sub macro3()
Dim myRange As Range
Dim myObj As Range
Dim keyWord As String
Set myRange = Range("A1:C8")
keyWord = "土屋"
Set myObj = myRange.Find(keyWord, LookAt:=xlPart)
If myObj Is Nothing Then
MsgBox "'" & keyWord & "'はありませんでした"
Exit Sub
End If
Dim msg As String
Dim myCell As Range
Set myCell = myObj
Do
msg = msg & "'" & keyWord & "'は" & myCell.Row & "行目" & myCell.Column & " にあります" & vbCrLf
Set myCell = myRange.FindNext(myCell)
Loop While myCell.Row <> myObj.Row
MsgBox msg
End Sub
ーー
Set myRange = Range("B:B")とするとＡ列の土屋は拾わなかった。

お礼

imogasiさん、回答ありがとうございます。
まずは自分の文章が要点を得ておらず申し訳ありません。
コードの添削をいただいたうえで、より良いやり方があればお教えいただきたいという趣旨でした。

時間と手間がかかってもよければ大抵のことはifとfor文で解決できるんですよね。
でも、vbaをきちんと勉強するつもりならそれ以外のことも学んでいかなければ……と思い、今回findを使って過去の注文履歴を表示させる練習をしたのですが、基礎がわかっていないことを痛感させられました。
今回は先に回答をくださった方がいたのでそちらの方をベストアンサーに選ばせていただきましたが、丁寧にご指摘いただきありがとうございました！

回答No.2

kkkkkm

> ワークシートのインデックス番号を変数iとして、for文で繰り返し計算させれば実行できるのでしょうか？

いけると思います。
ループごとの最後に
For i = 1 To Sheets.Count
処理
Set mRange = Nothing
Next
みたいにオブジェクトの開放を入れておいた方が無難だと思います。

お礼

kkkkkmさん、重ね重ねありがとうございます！
最初にご指摘いただいた点を修正し、for文を入れたらうまくいきました！