[[20040720011616]]　『検索する語句』（Nicky）　ページの最後に飛ぶ

『検索する語句』（Nicky）

初めまして。このサイトには、すばらしいマクロの達人がみえると、友達から伺いました。どなたか、教えてください。

例えば、ユーザーフォーム1 のテキストボックス1 に語句を入力します。次に、その語句を、Sheets1 の A列から検索して、検索でヒットした語句をユーザーフォーム2 のリストボックス1 に、縦に列挙させるには、どのようにすれば宜しいでしょうか？検索方法は、全てが完全に一致していなくても OK としたいです。漢字は別として、ひらがなで”あ”と入力したら、”ア”もヒットできればいいです。あと、英語の大文字、小文字も問わず、また、全角、半角も問わずがいいですが、やり方が分かりません。

さらに、ユーザーフォーム2 の列挙されたリストボックスから、どれかを選んだら、ユーザーフォーム3 のテキストボックス1～4に、A列からD列が表示されればいいです。

厚かましい要望ですが、どなたか、お知恵を御願いします。

 とりあえずは上部の「エクセルマイスター」のリンクから
バックナンバー「コンボボックスの連携」をご参考に。
https://www.excel.studio-kazu.jp/mag2/backnumber/mm20040706.txt
すべてのレコードに検索するためのデータ（振り仮名の一文字目など）を
与えておくと操作性はよくなるはずです。
(KAMIYA)

 検索でなく、オートフィルタは使えませんか？

  (INA)

(KAMIYA) さん、ありがとうございます。でも、私の知識が乏しいのか、理解が悪いのか・・・上のリンク先、拝見しましたけど、あれって、リストに定義させたときではないのでしょうか？

(INA) さん、オートフィルタは、考えたんですが、行数だけでも12,000ほどありまして、オートフィルタかけた後に探すのが大変かと。ひょっとして、オートフィルタ機能のマクロ化でしょうか？

お二人も含めまして、もう少しヒント頂けないでしょうか？宜しく御願い致します。　　(Nicky)

 Nickyさんがどれほどのスキルの持ち主なのかがわからないのと、
具体的にはどのようなデータを扱っているのがわからないこと、また、
検索方法を「あいまい」なものをご希望であることで戸惑っています。
検索に関して言えば、今のコンピュータは「そのものずばり」を検索
するのが得意で、「だいたいこんなの」を認識するのは苦手です。
　
 ＞あれって、リストに定義させたときではないのでしょうか？
別のコントロールの結果で目的のコントロールの内容を変更するという
意味で紹介させていただきました。
　
私の職場のマクロ使用例を書きます。
【顧客住所録】キーワード一文字入力でリストボックスに該当顧客名表示の例
（顧客名簿にはフリガナフィールドをあらかじめつくってあります）
・一時シートを作成
・テキストボックスへ顧客名先頭一文字を入力
・オートフィルタでフリガナフィールドにて例えば"=ア*"をフィルタリング
・フィルタ結果を一時シートへコピー
・一時シートの対象フィールドをリストボックスのRowSourceとして指定。
　
このようなやり方です。
「コンボボックスの連携」を基本としてオートフィルタを組み合わ
せたものに過ぎません。フリガナで絞り込む方法なのでご希望のも
のとは様相が違うかもしれませんが。
一時シートへデータをコピーするのは一見二度手間な感じですが、
フィルタ結果を１アイテムずつAddItemしていくよりもRowSourceで
範囲指定するほうが件数が多い場合、処理が早く記述も簡単だった
という理由によります。
ただ、このような方法でも、
＞行数だけでも12,000ほどありまして、オートフィルタかけた後に探すのが大変かと。
この状況は変わりません。
シート上でさがすか、リストボックス内で探すかの違いしかないので
もう少し絞込みをするデータを与えないと楽にはならないと推測します。
(KAMIYA)

(KAMIYA) 様、ありがとうございます。

＞具体的にはどのようなデータを扱っているのがわからないこと
実は、辞書検索を作っております。A列には日本語、B列には日本語に対応した英語、C列には日本語に対応した中国語、D列には中国語のピンイン、E列には日本語に対応したドイツ語です。私の希望では、ユーザーフォームに、4つのテキストボックスを作り、検索すれば、日本語、英語、中国語、ドイツ語からでも検索が出来る様な感じです。

例えば、日本語検索用のテキストボックスに、私と入力したら、検索結果に、A列の中から”私”を含む語句がユーザーフォーム2のリストボックスに列挙され、一つをクリックすると、それに対応した、英語、中国語、ドイツ語（Offset(,1)～Offset(,3)でいいと思います）が、ユーザーフォーム3に表示される形が理想です。

このサイトの単語検索機能のようなものと思います。空欄スペースのあとに語句を入力したら、両方吟味とかって、私には考えられないので。。。

＞Nickyさんがどれほどのスキルの持ち主なのかがわからない
これを言われてしまうと。。。実際、私にはマクロは難しいです。マクロの記録と、本やインターネットを参考にしながら、頑張っている状態です。私なりに厚かましい要望を出しましたが、

１．上記のような検索方法

２．考えているのは、UserForm2 を Unload させてから、UserForm3 を Show させよう

としているのですが、UserForm3 を閉じたときに、再度 UserForm2 を表示させて、かつ、検索結果を残している方法
が分かれば、何とかなりそうなんですが。。。

宜しく御願い致します。

 >オートフィルタ機能のマクロ化でしょうか？ 
 そうです。

 >１．上記のような検索方法
 「検索」だと１件ずつになるので、フィルタの方が速いと思います。

 >UserForm3 を閉じたときに、再度 UserForm2 を表示させて、
 >かつ、検索結果を残している方法
 閉じるときに、unload しないで hide すればよいと思います。

  (INA)

 TO INAさん
回答者二人でレスつけると混乱しそうなので
もしよければ後のフォローお願いできますか？
オートフィルタで抽出＞リストボックスへアイテム読み込み
という方針は同じようです。
(KAMIYA)

 方針が同じであれば、その方向で回答するように気を付けます。
 だれがVBAのコードを書いても、ほとんど同じようになると思いますし。(^_^;)

  (INA)

 面白そうなんで、私もよせてくだはい。
 お二人の達人に割り込んでと、お思いでせうが、何を隠そうこの弥太郎めもれっきと
 した達人の一人なんでっせぇ。
 ただし、「パクリマクロの」と言う冠を戴いてますけどな。（笑）

 まぁ、本格的なんは達人さんにお任せするとして、息抜きに私の冷やかしコードもお試
 してみて下はい。

 新しいブックに
 1)フォーム１　テキストボックス４　コマンドボタン１
 2)フォーム２　コンボボックス１　コマンドボタン１
 3)フォーム３　テキストボックス４　コマンドボタン３
 を作って下さい。

 4)標準モジュールに下のコードをコピペ
 '-----------------------
 Public maxrow As Long
 Public ptxt(1, 4) As String
 '--------------------------
 Sub test()
    UserForm1.Show
 End Sub
 '--------------------------

 5)フォーム１のモジュールに下のコードをコピペ

 '--------------------------
 Private Sub CommandButton1_Click()
    Dim f_data As String, data As String
    Dim i As Long
    maxrow = Range("a65536").End(xlUp).Row
    f_data = StrConv(TextBox1.Text, vbUpperCase)
    f_data = StrConv(f_data, vbWide)
    f_data = StrConv(f_data, vbHiragana)
        For i = 1 To maxrow
            data = StrConv(Cells(i, 1), vbUpperCase)
            data = StrConv(data, vbWide)
            data = StrConv(data, vbHiragana)
        If data Like "*" & f_data & "*" Then
            UserForm2.ComboBox1.AddItem Cells(i, 1)
        End If
    Next i
    Unload Me  'フォーム１を閉じる

    UserForm2.Show  'フォーム２を開く
 End Sub

 '---------------------------------
 Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal  Shift As Integer)
    If KeyCode = vbKeyReturn Then  'TextBox1が埋められていたら
        If TextBox1.Text <> "" Then  'CommandButton1にフォーカスを移す。
            CommandButton1.SetFocus
        End If
    End If
 End Sub
 '------------------------------------

 6) フォーム２のモジュールに下のコードをコピペ

 '----------------------------------
 Private Sub ComboBox1_Change()
    For i = 1 To maxrow
        If ComboBox1.Text = Cells(i, 1) Then
            ptxt(1, 1) = Cells(i, 1)  'コンボを選択すると
            ptxt(1, 2) = Cells(i, 2)  'その行を変数に格納
            ptxt(1, 3) = Cells(i, 3)
            ptxt(1, 4) = Cells(i, 4)
            Exit Sub
        End If
    Next i
 End Sub
 '---------------------------------
 Private Sub CommandButton1_Click()
        UserForm2.Hide  '当該フォームを非表示
        UserForm3.Show  'フォーム３を表示
 End Sub
 '---------------------------------

 7) フォーム３のモジュールに下のコードをコピペ
 '--------------------------------
 Private Sub CommandButton1_Click() 'フォーム２に戻る
    Unload Me
    UserForm2.Show
 End Sub
 '------------------------------
 Private Sub CommandButton2_Click()  'フォーム１に戻る
    Unload Me
    UserForm2.ComboBox1.Clear
    UserForm1.Show
 End Sub
 '------------------------------
 Private Sub CommandButton3_Click()  'マクロ終了
    Unload Me
    Unload UserForm2
    End
 End Sub
 '----------------------------
 Private Sub UserForm_Initialize()  'フォーム３を表示時に
    TextBox1.Text = ptxt(1, 1)    'テキストボックスにデータ表示
    TextBox2.Text = ptxt(1, 2)
    TextBox3.Text = ptxt(1, 3)
    TextBox4.Text = ptxt(1, 4)
 End Sub
 '----------------------------------

 っとまあこんな案配に準備して貰うて
 シートのA列からD列まで適当にデータを放り込んで下はい。
 testを実行してテキストボックス１（それ以外は対処するように作ってまへん）
 にデータの一部、例えば　「私」を含むデータを検索したかったらそのように入力
 しておくんなはれや。
 フルネームは勿論、半分でも全て検索します。（多分）
 ただ、オートフィルターを使ってまへんから処理時間は？ですワ。

 金曜日やったら超のつく達人がレスつけてますけど、それ以外はゼニ儲けに勤しんどる
 か自鯖のリニューアルに精出してますさかいなぁ。（笑）
 　　　ほな・・・（弥太郎）

すいません。なかなか忙しくって、書き込みが遅くなってしまいました。

(INA)様、(KAMIYA)様、ありがとうございます。hide という方法、私の勉強不足です。Unload しか知らなかったので、UserForm2 よりも大きな UserForm3 を作って、UserForm3 表示時に UserForm2 をUnloadしない方法を取っていました。勉強してみます。

(弥太郎)様、・・・難しいです。私の知らないところがゴロゴロ。早速試してみました。すると。。。

UserForm1 の TextBox1 ～ 4 に語句入力後、CommandButton1 を実行すると、UserForm2 の ComboBox1 に何も表示されないんですが・・・私のやり方がまずかったのでしょうか。

確認させてください。
(弥太郎)様のコードは、
UserForm1 の TextBox1 ～ 4 が、A列～D列の語句を入力して CommandButton1 にて検索。結果を UserForm2 の ComboBox1 に表示。選択してから Commandbutton1 にて、その行のA列～D列を、TextBox1 ～ 4 に表示。ComboBox1 で検索結果、ComboBox2 で検索画面に戻り、ComboBox3 で終了させるというものですよね。

すいません。どこか間違っているかもしれませんが、UserForm2 のとこまで行かなくって・・・アドバイス御願い致します。

あと、(弥太郎)様のコードだと、やはり検索に時間がかかるんですか？各行一つ一つみるからなんですね？私は、オートフィルタのマクロ化、やったことないんで、ちょっと難しいですが。。。　　　　　　　　　　　　　　(Nicky)

 '(General)                '(Declarations)
 Public maxrow As Long
 Public ptxt(1, 4) As String
 '------------------------------------
 Sub test()
    UserForm1.Show
 End Sub

 標準モジュールを捲ってみてくだはい。
 Public にカーソルをもっていったらその上の欄の表示がGeneral  Declarations
 になってまっか？
 コレも重要ですさかいキチンとやっといてくだはい。

 まさか、Nickyさん、TextBoxの２から４にも入力したんやおまへんやろなぁ。
 前文を読んで貰たらお分かりでっしゃろけど、このコードはTextBox1の入力しか
 対応しまへん（それ以外は省略）で。
 その為にボックス１に入力確定（エンターキーを叩く）するとコマンドボタンに
 フォーカスが移るように作ってありまんねん。

 それとコンボの↓は当然クリックしてくれましたわなぁ。

 原因として考えられるんはそんなとこですわ。
 初めから読み直して間違いがないか確認してみてくだはい。

 止まる原因になりそうなコードの左境界線をクリックすると行全体が茶色に染まり
 ますさかい、そこから「F8」でデバックするんも方法の１つです。
 ptxtにちゃんと入力されとるかなどはポインターを合わせれば良くわかります。
 　　　　　　（弥太郎）

(弥太郎)様、お手数お掛け致しまして、すいません。

私の経過を御連絡します。

まず、(弥太郎)様がおっしゃるように、Public にカーソルをもっていったらその上の欄の表示がGeneral Declarations になっています。TextBoxについては、(弥太郎)様のコードを見た限り、TextBox1にしか入力を試していません。2～4に対応していないのが分かったためです。ComandButton1 はクリックしています。

私の操作が悪いのか、理解が乏しいのか・・・
ステップインしますと、

        If data Like "*" & f_data & "*" Then

のところで、A列と同じ語句をUserForm1のTextBox1に入力しても、

　　　　UserForm2.ComboBox1.AddItem Cells(i, 1)

に行かないんですが・・・私にもう少し知識があればいいのかもしれませんが。(弥太郎)様、お手数をお掛け致しますが、御指導御願い出来ますでしょうか？　　　　(Nicky)

(弥太郎)様、出来ました。勘違いしていました。申し訳ありません。。。

お尋ねしても宜しいでしょうか？

        If data Like "*" & f_data & "*" Then

って、どのように解釈するのですか？もし宜しければ、御指導願います。　　　(Nicky)

 それを説明するにはコードの流れから説明する必要がおます。
 1)   f_data = StrConv(TextBox1.Text, vbUpperCase)
 2)   f_data = StrConv(f_data, vbWide)
 3)   f_data = StrConv(f_data, vbHiragana)
        For i = 1 To maxrow
 4)           data = StrConv(Cells(i, 1), vbUpperCase)
 5)           data = StrConv(data, vbWide)
 6)           data = StrConv(data, vbHiragana)
        If data Like "*" & f_data & "*" Then

 1)～3)で f_dataと言う変数にTextBox1を全角大文字に変換したデータを格納する。
 4)～6)でCells(i,1)を全角大文字に変化して変数dataに格納する
 If data Like "*" & f_data & "*" Then
 従って上のコードは、もし変数dataの中に変数f_dataが含まれていればと言う意味です
 す。
 例えばA列の任意のセルに
 a)アイスピック
 b)アイマスク
 c)ｱｲｽﾏﾝｼﾞｭｳ（あれぇへん、あれぇへん）
 d)あいじょう
 e)アイスバーン
 f)キャッツアイ
 g)あいすぼっくす

 ってな塩梅でデータがあったとします。
 フォーム１のTextBox1に「あい」と入力すれば（アイでもよろしい）前述のデータが
 そっくりコンボボックスに転送されるようになっとります。
 また「アイス」（あいす）と入力すると
 a,c,e,gがコンボに転送されます。
 以上、ご理解頂けましたでせうか？
 　　　　今日もヤケに暑い（弥太郎）

(弥太郎)様、非常に御丁寧な御説明、ありがとうございます。
凄いです。凄いです。想像の域を超えていました。
上記の状態で、”アイ”でもいいんですよね。凄いです。ｱｲｽﾏﾝｼﾞｭｳ（ないと思いますが・・・）のように、半角でも”あいす”から検索可能なんですか。試してみます。

でも、このコードは非常に難しいです。私は、If ～ Then に、”= ”などがないんで、理解に苦しみますが、頑張って勉強してみます。

暑いですが、お体、お気を付けくださいね。では、また、宜しく御願い致します。　　(Nicky)

(弥太郎)様、先日は、どうもありがとうございました。

また、行き詰ってしまいましたので、御教授を御願い致します。

(弥太郎)様のコードを多様化して、A～E列に対応できるようなコードを考えました。

UserForm1 のコードに、

    maxrow = Range("b65536").End(xlUp).Row
    f_data = StrConv(TextBox1.Text, vbUpperCase)

および

            data = StrConv(Cells(i, 2), vbUpperCase)

さらに

           UserForm2.ComboBox1.AddItem Cells(i, 2)

に変更したコードを加えました。

UserForm2 に、B列の検索結果を表示させようとすると、UserForm2 のコードを、

       If ComboBox1.Text = Cells(i, 2) Then

に変更させる必要があると思いますが、そうすると、A列の検索結果が表示されなくなってしまうと思います。

A～E列まで、全てに対応させた、UserForm2 の、いいコードって、ありますでしょうか？

お手数ですが、再度、御教授願います。　　　　　　　(Nicky)

 Nickyさん、これはTextBox１個で検索しようってんやおまへんやろなぁ。
 ５個のTextBoxにそれぞれ入力せなんだらEnglishとGermany、あるいは日本語と中国
 語とごちゃ混ぜに検索したら数は多くなるし思わんとこに表示される危険性がありま
 すから、それは止めときまひょでぇ。
 それと検索はフォーム１のいずれかのテキストボックスにしときましたけど、（一旦
 フォーム３まで進んで新たに検索する）２個、３個と同時に検索したいばやいは
 あんさんが頭捻って考えておくんなはれ。

 フォーム１に５個のTextBoxとコマンドボタン１個を配置してくだはい。
 それぞれのTextBoxのIMEModeはお好きなように設定してもらいます。
 フォーム２は従来通りでOKです。
 フォーム３ですけどナ、TextBox5個にCommandButton４を追加して
 Captionを　CommandButton1 →継続　（フォーム２に帰るボタン）
 　　　　　　　　　　　　2 →更新　（コンボの中身を消してフォーム１に帰る）
                         3 →終了　（作業終了）
                         4 →継続-更新　（コンボの中身はそのままでフォーム１）
 という塩梅（内容さえ分かればなんでも宜しいんやで）にしときまひょか。
 　
 標準モジュールに
 '------------------------
 Public n As Integer
 Public flag As Boolean      '←この宣言はGeneral Declarationsで
 Public maxrow As Long
 Public ptxt(1 To 5) As String
 '---------------------------
 Sub test()
     UserForm1.Show
 End Sub
 '-----------------------------

 フォーム１のモジュールに
 '----------------------------
 Private Sub CommandButton1_Click()
    Dim f_data As String, data As String
    Dim i As Long
    maxrow = Cells(65536, n).End(xlUp).Row
    f_data = StrConv(Me.Controls("TextBox" & n).Text, vbUpperCase)
    f_data = StrConv(f_data, vbWide)
    f_data = StrConv(f_data, vbHiragana)

        For i = 1 To maxrow
            data = StrConv(Cells(i, n), vbUpperCase)
            data = StrConv(data, vbWide)
            data = StrConv(data, vbHiragana)
            If data Like "*" & f_data & "*" Then
                UserForm2.ComboBox1.AddItem Cells(i, n)
            End If
        Next i
    Unload Me

    UserForm2.Show
 End Sub
 '------------------------------------
 Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        If TextBox1.Text <> "" Then
            n = 1
            CommandButton1.SetFocus
        ElseIf flag = False Then
            n = 0
        End If
    End If
 End Sub
 '---------------------------------------
 Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        If TextBox2.Text <> "" Then
            n = 2
            CommandButton1.SetFocus
        ElseIf flag = False Then
            n = 0
        End If
    End If
 End Sub
 '------------------------------------------
 Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = vbKeyReturn Then
        If TextBox3.Text <> "" Then
            n = 3
            CommandButton1.SetFocus
        ElseIf flag = False Then
            n = 0
        End If
    End If

 End Sub
 '---------------------------------------------
 Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        If TextBox4.Text <> "" Then
            n = 4
            CommandButton1.SetFocus
        ElseIf flag = False Then
            n = 0
        End If
    End If

 End Sub
 '----------------------------------------------
 Private Sub TextBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        If TextBox5.Text <> "" Then
            n = 5
            CommandButton1.SetFocus
        ElseIf flag = False Then
            n = 0
        End If
    End If

 End Sub
 '--------------------------------------------------

 フォーム２のモジュールに
 '---------------------------------------------------
 Private Sub ComboBox1_Change()
    For i = 1 To maxrow
        If ComboBox1.Text = Cells(i, n) Then
            ptxt(1) = Cells(i, 1)
            ptxt(2) = Cells(i, 2)
            ptxt(3) = Cells(i, 3)
            ptxt(4) = Cells(i, 4)
            ptxt(5) = Cells(i, 5)
            Exit Sub
        End If
    Next i
 End Sub
 '----------------------------------------------
 Private Sub CommandButton1_Click()
        UserForm2.Hide
        UserForm3.Show
 End Sub
 '---------------------------------------------

 'フォーム３のモジュールに
 '---------------------------------------
 Private Sub CommandButtn4_Click()
    Unload Me
    flag = True
    UserForm1.Show
 End Sub
 '---------------------------------------
 Private Sub CommandButton1_Click()
    Unload Me
    UserForm2.Show
 End Sub
 '------------------------------------
 Private Sub CommandButton2_Click()
    Unload Me
    UserForm2.ComboBox1.Clear
    flag = False
    UserForm1.Show

 End Sub
 '----------------------------------------
 Private Sub CommandButton3_Click()
    Unload Me
    Unload UserForm2
    End
 End Sub
 '-------------------------------------
 Private Sub UserForm_Initialize()
    TextBox1.Text = ptxt(1)
    TextBox2.Text = ptxt(2)
    TextBox3.Text = ptxt(3)
    TextBox4.Text = ptxt(4)
    TextBox5.Text = ptxt(5)

 End Sub

 以上ですワ。
 早とちりせんと慎重に事を運んでおくんなはれや。（笑）
 　　　　　　ほな・・・（弥太郎）

(弥太郎)様、出来ました。
素晴らしいです。ありがとうございました。

また、分からないことがあったら、宜しく御願い致します。　　　　(Nicky)

コメント返信：

[ 一覧(最新更新順) ]

[[20040720011616]] 『検索する語句』（Nicky） ページの最後に飛ぶ

[[20040720011616]]　『検索する語句』（Nicky）　ページの最後に飛ぶ