[[20180913142100]]　『セル検索・移動方法』（黒部ダム）　ページの最後に飛ぶ

『セル検索・移動方法』（黒部ダム）

ログを見ていたのですが正解に辿りつけず、申し訳ありませんがご質問させていただきます。
１つのブックに管理状況を確認するのにカテゴリ別にシートを分けてリストを作成しています。全てのシートはA～J列まで入力があります。
そこで全シートを対象とした検索をできるようにしたいと思っています。

シート１のセルＫ１に入力した文字を検索し、
・該当データがある場合はそのセルに移動
・該当データがない場合はメッセージボックスで「該当データがありません」
と表示するにはどのようなコードを作成すればいいのでしょうか

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

 ブック単位で検索機能を使用するのではダメでしょうか？
（コナミ） 2018/09/13(木) 15:33

・シート上で、CTRL+F してください。
・「検索する文字列」を入力します。ワイルドカードも使えます。
・「検索と置換」画面で、「検索場所」を「ブック」にします。
・「すべて検索」します。
・一致が無ければ、「検索条件に一致するデータが見つかりません。」と表示。あれば一覧が表示されます。1つを選んでクリックすると、該当セルに飛びます。

多少文字列が違いますし、K1セル限定ではないですが、おっしゃっている内容を満たしているのでは？
または、どうしてもご自分でコーディングしたいならば、Application.Findメソッドについて調べてみてください。
（???） 2018/09/13(木) 15:40

ダブった。けど重要なことが記載されていないのでそのまま投稿。

検索→オプション＞＞→検索場所を「ブック」にすればブック全体から検索してヒットした場所に飛べることは・・・もちろんご存知ですよね！？

これをマクロの記録で記録すればVBAコードになるのですが、検索場所を「ブック」「シート」に切り替えるパラメータが用意されていませんので、ユーザーが直前に使用した設定になってしまいます。

変更方法は私にはわかりませんので、有識者におまかせしますが
・検索にワイルドカードなどを使うか？
を書いておいたほうが良いと思います。

ひとまず手動で出来ますよって紹介だけです。
（名無し） 2018/09/13(木) 15:42

説明足らずで申し訳ありません
ctrl+F や検索ではなく、Application.Findがよく分からないので教えて頂けたらと思います。
記録を付けてコードを作ってもよく分からかったのでお願いします。

（黒部ダム） 2018/09/13(木) 16:15

 >全シートを対象とした検索をできるようにしたいと思っています。
 >Application.Findがよく分からないので教えて頂けたらと思います。
 この2つをどうやって結びつけるのでしょう？
（seiya） 2018/09/13(木) 16:28

まず自分で考えて作ったコードを貼って下さい。
よくわからないから教えろと言われても判りません。
作ったコードを貼って、どのような作用を起こしたいのか再度明記して、
その上でどうして動かないのか教えて欲しい　と言われないとアドバイスは誰にも出来ません。

Application.Findについて知りたいならば、上でも言われている通り検索かけて自分で調べて下さい。
ここで説明するより詳細に解りやすく書かれた解説サイトがいくらでもありますので。
（名無） 2018/09/13(木) 16:29

それならここをまず読んで見ることをオススメします。

第98回.Findメソッド（Find,FindNext,FindPrevious）
https://excel-ubara.com/excelvba1/EXCELVBA398.html

こちらも、VBAのFindメソッドの使い方には注意が必要です
https://excel-ubara.com/excelvba4/EXCEL225.html

先に述べたとおり検索場所を「ブック」にする方法を（私は知らない）のもあって、
こちらのページを参考に自力で検索コードを書いてます。
（名無し） 2018/09/13(木) 16:30

 検索する文字列が条件によってかわったり、何らかのイベント時に自動で検索セルに移動してほしい
 などVBAを使うには相応の理由やメリットがあります。

 黒部ダムさんの言うように、K1セルに入力したらそこへ飛ぶ
 というだけならVBAでコーディングするメリットがないように思えます。

 だってそういう機能があるんです。最初から。
 素人が記録を使ったり人に聞いて一生懸命作らずとも
 プロがコーディングしてくれたものがあるんです。

 一応書いておきますけど、

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Ws As Worksheet, Fc As Range
        If Target = Range("K1") Then
            For Each Ws In Worksheets
                Set Fc = Ws.Cells.Find(Target.Value, LookAT:=xlWhole)
                If Not Fc Is Nothing Then
                    If Fc.Address <> Target.Address Then
                        Ws.Select
                        Fc.Select
                        Exit Sub
                    End If
                End If
            Next Ws
        End If
    End Sub

（TAKA） 2018/09/13(木) 16:39

seiyaさんがヒントとなる視点を書いてくれていますが、Findでは1つのシートの中からしか文字列を探せません。なので、全シート分ループさせ、いちいちFindを実行していくことになるでしょう。 TAKAさんのロジックは正攻法だと思いますよ。

CTRL+F なら既に最速で多機能なものが提供されているのに、わざわざ遅くて面倒なマクロを書かなければいけないのか。それも他人のために。そう思うと、私は頑張る気にはなりません。同じ事をするコードなんて、Webを探せば見つかるだろうし。

そして、目的は管理状況を確認する事なんですよね？実は全てのシートは同じレイアウトであり、確認したい情報は同じ列に書かれていたりしませんか？全シートの全セルから探す必要なんて無かったりしませんか？
（???） 2018/09/13(木) 16:46

 あ、メッセージボックス忘れてました

   Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Ws As Worksheet, Fc As Range
        If Target = Range("K1") And Target.Value <> "" Then
            For Each Ws In Worksheets
                Set Fc = Ws.Cells.Find(Target.Value, LookAT:=xlWhole)
                If Not Fc Is Nothing Then
                    If Fc.Address <> Target.Address Then
                        Ws.Select
                        Fc.Select
                        Exit Sub
                    End If
                End If
            Next Ws
            MsgBox "該当データがありません"
        End If
    End Sub

（TAKA） 2018/09/13(木) 16:47

 >１つのブックに管理状況を確認するのにカテゴリ別にシートを分けてリストを作成

 根本的な話なんですが、管理するならまずはデータは1つにまとめて
 必要ならそこからカテゴリ分けするような仕様の方がいいですよ。
 そうすれば探すのはまとめたシートだけで済みます。

 ご自身が作成しているブックならそのように作り変えるのも検討してください。
（コナミ） 2018/09/13(木) 16:51

 突っ込みを入れたくなるコードが投稿されてるけど...
 Find method で検索して、そのシートのセルに移動するなら

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim ws As Worksheet, r As Range, flg As Boolean
     If Intersect(Target, Range("k1")) Is Nothing Then Exit Sub
     For Each ws In Worksheets
         If (ws.Visible = -1) * (Not ws Is Me) Then
             Set r = ws.Cells.Find(Range("k1").Value, , , 1)
             If Not r Is Nothing Then
                 Application.Goto r
                 flg = True: Exit For
             End If
         End If
     Next
     If Not flg Then MsgBox "該当なし"
 End Sub
（seiya） 2018/09/13(木) 17:03

校内に同じような記事がありました。

[[20050915225243]] (8885)『検索されたセルに移動しセレクトされた状態にする』

（　　　HO　　　　　） 2018/09/13(木) 17:12

 >突っ込みを入れたくなるコードが投稿されてるけど...

 これ言う必要ありますか？
 私VBA覚えてまだ一年くらいで知らない関数もメソッドもたくさんあります。
 私が知ってるものだけでコーディングしているので幼稚なコードかもしれませんが
 重大な間違いでもありましたか？失礼だと思います。
（TAKA） 2018/09/13(木) 17:19

 1)
        If Target = Range("K1") And Target.Value <> "" Then
 例えばK1に10があったとして、他のセルに10を入力したときに走るよね?

 2) もし非表示のシートがあってそのシートに検索文字列があったらSelectできないよね？

 と言えばよかったのかな？
 私ならコードをアップする前にその程度は検証するけど...
 回答するのに経験が1年でも10年以上でも関係ないと思うけど？

 黒部ダムさん、
 もしK1が存在するシートも検索対象にするなら

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim ws As Worksheet, r As Range, myFind As Range
     If Intersect(Target, Range("k1")) Is Nothing Then Exit Sub
     For Each ws In Worksheets
         If ws.Visible = -1 Then
             Set r = ws.Cells.Find(Range("k1").Value, , , 1)
             If Not r Is Nothing Then
                Set myFind = r
                If r.Address(, , , 1) = Range("k1").Address(, , , 1) Then
                    Set r = ws.Cells.FindNext(r)
                    If r.Address(, , , 1) <> Range("k1").Address(, , , 1) Then
                        Set myFind = r
                    Else
                        Set myFind = Nothing
                    End If
                End If
             End If
             If Not myFind Is Nothing Then Application.Goto myFind: Exit For
         End If
     Next
     If myFind Is Nothing Then MsgBox "該当なし"
 End Sub
（seiya） 2018/09/13(木) 17:30

 seiyaさん
 はい、そのように具体的に言っていただければ文句はありませんでした。
 間違いを指摘していただけること自体は勉強になるのでむしろうれしいです。
 馬鹿にしたような言い方が気に入らなかっただけです。
 長々と失礼しました。

 黒部ダムさん
 関係ない話をしてすみませんでした。
（TAKA） 2018/09/13(木) 17:41

TAKAさんの投稿って1,2年前の自分を見ているようで気持ちは分からなくもないんですよね。

話がかぶりますけど↓の部分、

 If Target = Range("K1") Then

おそらく、TargetがK1セルか判定したかったんだとおもいますが、

 If Target.Value = Range("K1").Value Then

という意味になっているので、TargetがK1セルと同じ値だったらTrueになっちゃいます。（←seiyaさん指摘済み）
さらに、Targetは複数セルになりうるので、その場合、型が一致しないってExcel君から怒られます。

では

 If Target.Address = Range("K1").Address Then

ならどうかという発想になるとおもいますが、これは、Targetが単一セルなら機能しますが、先の通り、複数セルの場合もありえますので、こんどはTargetにK1セルが含まれていても、偽を返すようになってしまいます。

このため、seiyaさんが提示されたように、Intersectメソッドで、TargetとK1セルの交差を求め、Nothingが返ってくる（=交差してない）ならK1セルは含まれていない。という判定をするのが一般的なアプローチかとおもいます。

また、非表示の～可能性については私は気づかなかったのでスルーしますけど、それを除いても

    If Not Fc Is Nothing Then
        If Fc.Address <> Target.Address Then
            Ws.Select
            Fc.Select
            Exit Sub
        End If
    End If

こんなに長くしなくても、「該当データがある場合はそのセルに移動」ということですから、

    If Not Fc Is Nothing Then
        Application.Goto Fc
        Exit Sub
    End Sub

で良いと思います。

（もこな２） 2018/09/14(金) 08:54

 もこな2さん丁寧にありがとうございます。
 言われてみればそうですよね。

 自分で使ってるシートモジュールも、他の質問で回答したときも
 Intersect を使っているのに、どうかしてました。。。

 Application.Goto は 初めて知りました！
 書きながら絶対もっとスマートなのありそうなのになぁ、と思っていたんで
 スッキリしました。勉強になりました。

 いつもありがとうございます。
（TAKA） 2018/09/14(金) 11:34

もこな２さん、TAKAさんへ

今回の事例では大丈夫（だと思う）のですが、
Application.Gotoの不具合には何度も苦しめられてきたので、
私はGotoは極力使わない方が良いと思います。

TAKAさんは初めて知ったとのことなので、多様しすぎないようにご注意下さい。

参考
https://excel-ubara.com/excelvba4/EXCEL272.html

私もボタン押したら飛ぶだけ！くらいの処理なら使うんですけどね・・・。
（名無し） 2018/09/14(金) 11:52

参照先が重いのか私の環境だとタイムアウトしてしまって見られませんが、
おっしゃるような不具合があるのであれば、確かに良くないですね。
（Application.Gotoを普段、多用しないので気づきませんでした。と言い訳・・・）

そうでであれば、ブック、シート、セルの順番でActive（Select）することも意味はありますね。
フォローどもです。

（もこな２） 2018/09/14(金) 13:10

上記URLは
【エクセルの真髄】のトップ > マクロVBA > マクロVBA技術解説 > Application.Goto使用時の注意
です。
別に重いページでは無かったのですが・・・回線の相性とかあるのですかね。

重要な部分だけ引用すると、
＞Excelのアプリケーションそのものが異常終了してしまう事が有ります。
＞Application.Gotoの実行以前に、かなりのシート操作（つまり複雑な処理）
＞が行われているマクロに限定されるようです。
＞短めのVBAコード（つまり簡単な処理）であれば、現象は発生していません。
とのことです。
私も”かなりのシート操作”かどうかは置いといて、Gotoを消したら強制終了が改善したことがありますので
余裕があるときは避けたほうが無難かなと思います。
（名無し） 2018/09/14(金) 13:50

コメント返信：

[ 一覧(最新更新順) ]

[[20180913142100]] 『セル検索・移動方法』（黒部ダム） ページの最後に飛ぶ

[[20180913142100]]　『セル検索・移動方法』（黒部ダム）　ページの最後に飛ぶ