Excel マクロ VBについて

質問

今ExcelでVBを使い表の作成を行っています。その中で複数のシートが
あり、各シートでは検索ができるのですが、TOPページ時にすべてのシートから条件に合うものを検索するというやり方がわかりません。
検索については以下のプログラムなのですが、これをどう変えればすべてのワークシート検索ができるかを教えてください。

' 検索クリック時の処理
Private Sub Command検索_Click()
MyTxt = Text検索.Value
If MyTxt = Empty Then
MsgBox ("検索するキーワードを入力してください")
Exit Sub
End If
Set MyData = Range("A3").CurrentRegion
Set MyFid = MyData.Find(MyTxt)
If MyFid Is Nothing Then
MsgBox ("該当するキーワードが見つかりません")
Else
MyKRow = MyFid.Row
MyKRow2 = MyFid.Row

For MyKcnt = 0 To 14

Val1(0, 0) = Cells(MyKRow, MyKcnt + 1).Value
MyKmo(0, MyKcnt) = Val1(0, 0)

Sheets("サーチ").Select
Cells(4, MyKcnt + 1).Value = Val1(0, 0)

Sheets("L").Select

' MyKmo(0, MyKcnt) = Cells(MyKRow, MyKcnt + 1).Value
Next

Sheets("サーチ").Select
Cells(4, 15).Value = MyKRow
Sheets("L").Select

MyREC = 1
Form検索L.LabelREC = MyREC
Form検索L.LabelROW = MyKRow
List結果.List = MyKmo

End If
End Sub

もし、説明不足の場合は補足しますので、どういう情報が必要かを
いっていただけるとありがたいです。
よろしくお願いします。

回答No.6

ham_kamo

あれ、おかしいですね。デグレしたかな。

＞追加する部分にいたっては、同じものが無く

のはずはないと思います。Myで始まる変数は、Command検索_Click()でもCommand次へ_Click()でも使われており、Command検索_Click()で設定した値がCommand次へ_Click()でも引き継がれているので、どこかでDim文ではなく、Publicで宣言されているはずなのです。

同じように変数sもPublicで宣言しないと、どのシートまで検索したか、という情報を保持できなくなります。プロシージャ内でDim文で宣言すると、プロシージャを抜けるとその変数の値は消されてしまいます。Command次へ_Click()の中でDim文でsが宣言されていても、それは別の変数として扱われるのです。

Command検索_Click()の中で、MyDataという文字の上で右クリックして、「定義」を選択してください。MyDataがPublicで宣言されている箇所へ自動的にジャンプします。(ThisWorkbookのシートか、あるいはModule1などの標準モジュールにあるのではないか、と予想していますが）

MyDataがPublicで宣言されているところが見つかれば、そこに
Public s As Worksheet
Public Found As Boolean
を追加してみてください。

お礼

ham_kamoさんの言われたとおりMyDataがPublicで宣言されている部分は
Module1のほうで見つけることができました。
教えていただいたとおりプログラムを挿入した結果TOPページから全ワークシート検索が可能にすることができました。
これも、ham_kamoさんのおかげです。ありがとうございました。

また、何かわからない部分があれば投稿することもあると思いますが
そのときはまた、ご指導のほうよろしくお願いします。

ありがとうございました。

回答No.5

ham_kamo

すみません、「MyTxtなどを定義している場所」という言葉があいまいだったので誤解を招いたかもしれません。

Set MyData = ...
とか、MyDataなどに値をセットしている場所ではなく、プロシージャの外のどこかに、

Public MyData as Range
Public MyTxt as String
Public MyFid as Range
などと、変数が宣言されているところがあると思うのですが、その場所に、

Public s As Worksheet
Public Found As Boolean

と追加してみてください。もしそうしているのにエラーが出るのなら、ちょっと私にも原因がわかりません。

あと、初期化処理の中でエラーが出る原因はちょっとわかりません。見たところ、一つも変数を使ってないのに「変数が定義されていないという」というエラーが出るのが不思議なのですが。
これもVBAの画面からF8でステップ実行させて、どこの行でエラーが出るか特定できると、原因究明の手がかりになるかもしれません。

補足

教えていただいたようにプログラムを入れたところエラーは出なくなったのですが、TOPページで検索を行うと全ワークシートからの検索ではなくなっているように思えるのです。
また、追加する部分にいたっては、同じものが無くおそらくDim～As Stringで定義してある部分ではないかと思いそこに追加したのですが、その場合Public s Asの部分はDimに変えても問題は無いでしょうか？

また、初期化処理のエラーについては私のミスによるものでした。申し訳ありませんでした。

何度もお聞きしてすみませんがよろしくご指導お願いします。

回答No.4

ham_kamo

あれ？おかしいですね。新しい変数は使ってないので、
Public s As Worksheet
Public Found As Boolean
これらをMyFidやMyTxtを定義しているのと同じ場所に書いておけば、それらの変数と同様にこのプロシージャの中でも参照できるはずなので、変数が定義されてないというエラーは出ないはずなのですが…。

「次へ」のボタンを押す代わりに、VBAの画面からCommand次へ_Click()をF8キーでステップ実行していったときに、どの行でエラーが出るか試してみていただけませんか？

補足

MyFidやMyTxtの同じ場所に移し変えるときSetごと書き直してもいいのですよね？
検索でも次への処理でもない初期処理の部分でエラーが出てしまっている状態です。エラーは、Private Sub UserForm_Initialize()でやはり
変数が定義されていないという風に出ています。
初期処理のほうは以下のようになっています。

' 初期化処理
Private Sub UserForm_Initialize()
Sheets("サーチ").Range("A1:Z200").Clear

Sheets("サーチ").Select

Cells(1, 1).Value = "検索結果"
Cells(1, 3).Value = "検索条件"

Cells(3, 1).Value = "型式"
Cells(3, 2).Value = "インダクタンス(H)"
Cells(3, 3).Value = "定格電流"
Cells(3, 4).Value = "L許容誤差"
Cells(3, 5).Value = "メーカー"
Cells(3, 6).Value = "サイズ"
Cells(3, 7).Value = "数量"
Cells(3, 8).Value = "DCR(Ω)"
Cells(3, 9).Value = "DCR誤差マスナス"
Cells(3, 10).Value = "DCR誤差プラス"
Cells(3, 11).Value = "自己共振周波数(GHz)"
Cells(3, 12).Value = "場所"
Cells(3, 15).Value = "レコード行数"

Sheets("TOP").Select
End Sub

何度も聞いてばかりですみませんが、よろしくご指導お願いします。

回答No.3

ham_kamo

なるほど、「次へ」の処理があるのですね。
そうすると、今どのシートを検索中か変数で保持しておかないといけないので、先ほどの回答で挿入した２つのDim文を外し、MyTxtやMyDataなどが定義されているのと同じ場所に、

Public s As Worksheet
Public Found As Boolean

を追加してください。
この変数を使って、次の検索の処理に手を加えてみました。
追加した処理内容としては、

・検索にヒットしたかどうかを判定する変数Foundの初期値をあらかじめFalseにセット
・FindNext()で先頭に戻ってもすぐに終了せずに、次のシートへ進み、改めてFind()で検索。以降、検索にヒットしなければループで順次シートを次に進め、最後まで行っても見つからなかったらループを抜ける。（FoundがFalseのままループを抜ける）
・検索にヒットしたら、検索した行を記録してFoundの値をTrueにセットし、ループから抜ける。
・FindNext()の結果が先頭に戻らず次に進んだ場合もFoundにTrueをセット。
・以上の処理を通過した後、Foundの値がFalseなら、MsgBoxを出してプロシージャから抜ける。Trueならそのまま処理を続行

という感じです。同じ環境がないためデバッグしてないので、もし正常に動作しなかったらまた補足をお願いします。

' 次へクリック時の処理
Private Sub Command次へ_Click()
　　On Error GoTo OnError

　　If MyTxt <> Text検索.Value Then
　　　　MsgBox ("先に[検索]ボタンを押してください")
　　　　Exit Sub
　　End If
　　If MyTxt = Empty Then
　　　　MsgBox ("先に検索するキーワードを入力し[検索]ボタンを押してください")
　　　　Exit Sub
　　End If

　　Found = False

　　Set MyFid = MyData.FindNext(MyFid)
　　MyKRow2 = MyKRow
　　MyKRow = MyFid.Row

　　If MyKRow <= MyKRow2 Then
　　　　s = s.Next
　　　　Do While Not s Is Nothing
　　　　　　Set MyData = s.Range("A3").CurrentRegion
　　　　　　Set MyFid = MyData.Find(MyTxt)
　　　　　　If Not MyFid Is Nothing Then
　　　　　　　　Found = True
　　　　　　　　MyKRow = MyFid.Row
　　　　　　　　MyKRow2 = MyFid.Row
　　　　　　　　Exit Do
　　　　　　End If
　　　　　　s = s.Next
　　　　Loop
　　Else
　　　　Found = True
　　End If

　　If Found = False Then
　　　　MsgBox ("検索が終わりました")
　　　　Exit Sub
　　End If

　　For MyKcnt = 0 To 14

　　Val1(0, 0) = s.Cells(MyKRow, MyKcnt + 1).Value
　　　　MyKmo(0, MyKcnt) = Val1(0, 0)

　　　　Sheets("サーチ").Select
　　　　Cells(MyREC + 4, MyKcnt + 1).Value = Val1(0, 0)

　　　　Worksheets.Select

　　　　' MyKmo(0, MyKcnt) = Cells(MyKRow, MyKcnt + 1).Value
　　Next

　　Sheets("サーチ").Select
　　Cells(MyREC + 4, 15).Value = MyKRow
　　Worksheets.Select

　　MyREC = MyREC + 1
　　Form検索H.LabelREC = MyREC
　　Form検索H.LabelROW = MyKRow
　　List結果.List = MyKmo

Exit Sub

OnError:
　　MsgBox ("先に[検索]ボタンを押してください")
　　Exit Sub

End Sub

補足

なんだか頼りっきりで、すみません。早速教えていただいたプログラムを挿入してみたのですがやはり、検索処理の部分で変数が定義されていないというエラーが出てしまうのですがこの場合は、やはりDim文を追加しなければいけないのでしょうか？
よろしく、ご指導お願いします。

回答No.2

ham_kamo

No.1です。補足拝見しました。
すみません、Dim文を使ってないので、勝手にOption Explicitは設定されてないのだと思って変数の宣言をはしょっていました。（変数は他のところでPublicで宣言されておられるのですね）

とりあえず先頭の Private Sub Command検索_Click() のすぐ下に
Dim Found as Boolean
Dim S as Worksheet
を入れて試してみてください。

補足

ありがとうございました。教えていただいたプログラムでTOPから全ワークシート検索ができるようになりました。
しかし、せっかく教えていただいて申し訳ないのですが、検索結果が複数ある場合、検索を行うときちんと1つ目は出てくるのですが2つ目以降が次への処理を行っても検出されないのです。
' 次へクリック時の処理
Private Sub Command次へ_Click()
On Error GoTo OnError

If MyTxt <> Text検索.Value Then
MsgBox ("先に[検索]ボタンを押してください")
Exit Sub
End If
If MyTxt = Empty Then
MsgBox ("先に検索するキーワードを入力し[検索]ボタンを押してください")
Exit Sub
End If

Set MyFid = MyData.FindNext(MyFid)
MyKRow2 = MyKRow
MyKRow = MyFid.Row

If MyKRow <= MyKRow2 Then
MsgBox ("検索が終わりました")
Exit Sub
End If

For MyKcnt = 0 To 14

Val1(0, 0) = Cells(MyKRow, MyKcnt + 1).Value
MyKmo(0, MyKcnt) = Val1(0, 0)

Sheets("サーチ").Select
Cells(MyREC + 4, MyKcnt + 1).Value = Val1(0, 0)

Worksheets.Select

' MyKmo(0, MyKcnt) = Cells(MyKRow, MyKcnt + 1).Value
Next

Sheets("サーチ").Select
Cells(MyREC + 4, 15).Value = MyKRow
Worksheets.Select

MyREC = MyREC + 1
Form検索H.LabelREC = MyREC
Form検索H.LabelROW = MyKRow
List結果.List = MyKmo

Exit Sub

OnError:
MsgBox ("先に[検索]ボタンを押してください")
Exit Sub

End Sub

これが次へというボタンを押した際の処理なのですがいかがでしょうか？教えていただいたプログラムで検出して検出結果が複数の場合次へを押すことで次に検出されたものが見れるようにできたらと思うのですが、いかがでしょうか？

たびたび、質問ばかりですみません。
よろしくご指導お願いします。

回答No.1

ham_kamo

細かい処理内容は追ってないですが、とりあえず

・検索にヒットしたかどうかを判定する変数Foundを追加し、初期値はFalse

・Forループを使ってすべてのシートを順番にサーチして、検索にヒットしたらFound=TrueとしてFor文を抜けて次の処理へ移る

・Forループを抜けてもFound=Falseのままなら、どのシートでも見つからなかったのでMsgBox()を出す。

という感じで手を加えてみました。

Private Sub Command検索_Click()
　　MyTxt = Text検索.Value
　　If MyTxt = Empty Then
　　　　MsgBox ("検索するキーワードを入力してください")
　　　　Exit Sub
　　End If

　　Found = False
　　For Each S In Worksheets
　　　　Set MyData = S.Range("A3").CurrentRegion
　　　　Set MyFid = MyData.Find(MyTxt)
　　　　If Not MyFid Is Nothing Then
　　　　　　Found = True
　　　　　　Exit For
　　　　End If
　　Next S

　　If Found = False Then
　　　　MsgBox ("該当するキーワードが見つかりません")
　　Else
　　　　MyKRow = MyFid.Row
　　　　MyKRow2 = MyFid.Row

　　　　For MyKcnt = 0 To 14

　　　　　　Val1(0, 0) = S.Cells(MyKRow, MyKcnt + 1).Value
　　　　　　MyKmo(0, MyKcnt) = Val1(0, 0)

　　　　　　Sheets("サーチ").Select
　　　　　　Cells(4, MyKcnt + 1).Value = Val1(0, 0)

　　　　　　Sheets("L").Select

　　　　　　' MyKmo(0, MyKcnt) = Cells(MyKRow, MyKcnt + 1).Value
　　　　Next

　　　　Sheets("サーチ").Select
　　　　Cells(4, 15).Value = MyKRow
　　　　Sheets("L").Select

　　　　MyREC = 1
　　　　Form検索L.LabelREC = MyREC
　　　　Form検索L.LabelROW = MyKRow
　　　　List結果.List = MyKmo

　　End If
End Sub

補足

早速のお返事ありがとうございます。
早速教えていただいたプログラムに入れ替えて行ってみたのですが、
Private Sub Command検索_Click()で変数が定義されていないというエラーが発生してしまうのですが、この場合どこを変えればいいのでしょうか？