よねさんのWordとExcelの小部屋 なんでも掲示板

HOME HELP 新規作成 新着記事 ツリー表示 トピック表示 検索 過去ログ

[ 最新記事及び返信フォームをトピックトップへ ]

■13296 / inTopicNo.1)  Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
  
□投稿者/ なお Mail -(2018/09/19(Wed) 12:52:07)
    いつも楽しく拝見させていただいております。
    さて、「Excel(エクセル) VBA入門:検索結果をリストボックスに表示する」を使用させていただいております。
    このなかで検索ボックスが2つですが、これを増やしたいと考えております。
    どのようにしたらよろしいかアドバイスをお願いしたいです。
    初心者の為、よろしくお願いします。
    また、当初のリストにコメントを入れておきたいのですが、こちらについてもアドバイスをお願いします。
引用返信/返信 削除キー/
■13297 / inTopicNo.2)  Re[1]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/09/19(Wed) 14:22:37)
    Like演算子を使った検索
    http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_find.html#like
    
    氏名と住所をキーに検索しています。
    氏名(Textbox1)は myData(i, 2) Like "*" & TextBox1.Value & "*" で文字列の比較をしています。
    住所(Textbox2) はmyData(i, 7) Like "*" & TextBox2.Value & "*" としています。
    血液型(Textbox3) を追加すると myData(i, 13) Like TextBox3.Value を追加すればよいことになります。
    血液型は A.B,O,ABの完全一致で検索するので前後の*は不要と考えます。
    If myData(i, 2) Like "*" & TextBox1.Value & "*" _
      And myData(i, 7) Like "*" & TextBox2.Value & "*" _
      And myData(i, 13) Like TextBox3.Value Then
    
    > 当初のリストにコメントを入れておきたい
    現在は    myData2(i, 1) = myData(i, 1)
        myData2(i, 2) = myData(i, 2)
        myData2(i, 3) = myData(i, 7)
    と、連番、氏名、住所が表示されていると思います。
    この辺をいじればよさそうな気がしますが、どのようなことをなさりたいのかが
    分かりませんので、具体例でも示してください。
    

引用返信/返信 削除キー/
■13298 / inTopicNo.3)  Re[2]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ なお Mail -(2018/09/20(Thu) 18:35:08)
    ご返答ありがとうございます。
    郵便番号や都道府県でも検索を追加したいと考えております。
    コンビボックス(cmb区分)とテキストボックス(textbox3)を追加して、検索は出来るようになったのですが、テキストボックスとコンビボックスを空欄にして検索すると、「インデックスが有効範囲にありません」と表示されてしまいます。
    どうしても回避できないですが、アドバイスいただけないでしょうか。

    ------以下入力内容---------------

    '検索を実行します。部分一致検索を行っています。
    Private Sub CommandButton1_Click()
    Dim lastRow As Long
    Dim myData, myData2(), myno
    Dim i As Long, j As Long, cn As Long

    ' If TextBox1.Value = "" Or cmb区分.Value = "" or TextBox3.Value = "" Then End

    '検索するデータを配列 myData に格納しています。
    With Worksheets("Sheet1")
    myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 6).End(xlUp)).Value
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    '配列 myData の中で検索で一致したデータを配列 myData2 に格納しています。
    ReDim myData2(1 To lastRow, 1 To 5)
    For i = LBound(myData) To UBound(myData)
    If myData(i, 2) Like "*" & TextBox1.Value & "*" And myData(i, 3) Like "*" & cmb区分.Value & "*" And myData(i, 4) Like "*" & TextBox3.Value & "*" Then
    cn = cn + 1
    myData2(cn, 1) = myData(i, 1)
    myData2(cn, 2) = myData(i, 2)
    myData2(cn, 3) = myData(i, 3)
    myData2(cn, 4) = myData(i, 4)
    myData2(cn, 5) = myData(i, 5)
    End If
    Next i

    '検索で一致したデータをリストボックスに表示します。
    With ListBox1
    .ColumnCount = 5
    .ColumnWidths = "30;70;70;100;100"
    .List = myData2
    End With

    End Sub
引用返信/返信 削除キー/
■13299 / inTopicNo.4)  Re[3]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/09/21(Fri) 07:46:02)
    2018/09/21(Fri) 10:55:22 編集(投稿者)
    2018/09/21(Fri) 09:21:05 編集(投稿者)
    
    
    何回か書き直しています。m(__)m
    
    こちらでコードを書いて試してみた結果、下のような感じでどうにか行けそうな気がしました。
    http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_listbox.html
    の一番下に追記していますので、参照するとわかりやすいと思います。
    
    都道府県名が"Sheet1!O2:O48"に入力されていて、コンボボックスに
    初期設定Private Sub UserForm_Initialize() で
            With ComboBox1
             .RowSource = "Sheet1!O2:O48"
             End With
            
    のように書いていあるときの例です。
    
    すると、下のコードのようにして検索が可能と考えました。
    なお、このコードは
    http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_listbox.html
    のデータで検索するものです。
    
    
    Private Sub CommandButton1_Click()
        Dim lastRow As Long
        Dim myData, myData2(), myno
        Dim i As Long, j As Long, cn As Long
        Dim key1 As String, key2 As String, key3 As String, key4 As String
        
     If TextBox1.Value = "" Then key1 = "*" Else key1 = "*" & TextBox1.Value & "*"
     If TextBox2.Value = "" Then key2 = "*" Else key2 = "*" & TextBox2.Value & "*"
     If TextBox3.Value = "" Then key3 = "*" Else key3 = "*" & TextBox3.Value & "*"
     
     Dim ListNo As Long
            ListNo = ComboBox1.ListIndex
                If ListNo < 0 Then
                    key4 = "*"
                Else
                  key4 = ComboBox1.List(ListNo)
                End If
                
            
            With Worksheets("Sheet1")
                myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 7).End(xlUp)).Value
                lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
            End With
        ReDim myData2(1 To lastRow, 1 To 3)
            For i = LBound(myData) To UBound(myData)
    '            If myData(i, 2) Like "*" & TextBox1.Value & "*" And myData(i, 7) Like "*" & TextBox2.Value & "*" Then
                If myData(i, 2) Like key1 And myData(i, 7) Like key2 And myData(i, 5) Like key3 And myData(i, 6) Like key4 Then
                    cn = cn + 1
                    myData2(cn, 1) = myData(i, 1)
                    myData2(cn, 2) = myData(i, 2)
                    myData2(cn, 3) = myData(i, 7)
                End If
            Next i
            
            
            With ListBox1
                .ColumnCount = 3
                .ColumnWidths = "30;70;70"
                .List = myData2
            End With
    
    End Sub
    

引用返信/返信 削除キー/
■13300 / inTopicNo.5)  Re[4]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ なお Mail -(2018/09/25(Tue) 13:16:39)
    ご返信ありがとうございました。
    再度ではありますが、アドバイスをお願い致します。

    下記の様にして、sheet1の3行目に入力されているリストから出るようにしてみました。
    その際に検索画面でコンビボックスやテキストボックスを空欄にして検索すると、「インデックスが有効範囲にありません。」と表示されてしまいます。
    回避方法はどのようにしたらよろしいでしょうか?

    --------------------以下入力内容-----------------------------

    '検索を実行します。部分一致検索を行っています。
    Private Sub CommandButton1_Click()
    Dim lastRow As Long
    Dim myData, myData2(), myno
    Dim i As Long, j As Long, cn As Long

    ' If TextBox1.Value = "" Or cmb区分.Value = "" or TextBox3.Value = "" Then End

    '検索するデータを配列 myData に格納しています。
    With Worksheets("Sheet1")
    myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 6).End(xlUp)).Value
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    '配列 myData の中で検索で一致したデータを配列 myData2 に格納しています。
    ReDim myData2(1 To lastRow, 1 To 5)
    For i = LBound(myData) To UBound(myData)
    If myData(i, 2) Like "*" & TextBox1.Value & "*" And myData(i, 3) Like "*" & cmb区分.Value & "*" And myData(i, 4) Like "*" & TextBox3.Value & "*" Then
    cn = cn + 1
    myData2(cn, 1) = myData(i, 1)
    myData2(cn, 2) = myData(i, 2)
    myData2(cn, 3) = myData(i, 3)
    myData2(cn, 4) = myData(i, 4)
    myData2(cn, 5) = myData(i, 5)
    End If
    Next i

    '検索で一致したデータをリストボックスに表示します。
    With ListBox1
    .ColumnCount = 5
    .ColumnWidths = "10;30;70;60;400"
    .List = myData2
    End With

    End Sub

    '----------------------------------------------
    'リストボックス内のデータをダブルクリックするとシートのデータを選択します。
    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    With Worksheets("Sheet1")
    .Range(.Cells(ListBox1.List(ListBox1.ListIndex, 0) + 2, 1), .Cells(ListBox1.List(ListBox1.ListIndex, 0) + 2, 13)).Select
    End With
    End Sub


    '----------------------------------------------
    'ユーザーフォームの初期設定:リストの全データを表示しています。
    Private Sub UserForm_Initialize()
    Dim lastRow As Long
    Dim myData, myData2()
    Dim i As Long, j As Long

    With Worksheets("Sheet1")
    myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 6).End(xlUp)).Value
    lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
    End With

    ReDim myData2(1 To lastRow, 1 To 3)
    For i = LBound(myData) To UBound(myData)
    myData2(i, 1) = myData(i, 1)
    myData2(i, 2) = myData(i, 2)
    myData2(i, 3) = myData(i, 3)
    Next i

    With ListBox1
    .ColumnCount = 3
    .ColumnWidths = "30;70;70"
    .List = myData2
    End With



    'ボックスリスト

    cmb区分.List = MakeUniqueList
    cmb区分.ListIndex = 1

    End Sub

    Function MakeUniqueList()
    Dim objDic As Object 'New Scripting.Dictionary
    Dim i As Long, j As Long
    j = 1
    Set objDic = CreateObject("Scripting.Dictionary")
    With Worksheets("Sheet1")
    '2行目から
    For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
    If Not objDic.Exists(.Cells(i, 3).Value) Then
    objDic.Add .Cells(i, 3).Value, j
    j = j + 1
    End If
    Next
    End With
    MakeUniqueList = objDic.keys
    End Function
引用返信/返信 削除キー/
■13301 / inTopicNo.6)  Re[5]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/09/25(Tue) 15:32:42)
    ↓のページで作成しているデータ、ユーザーフォームを使って
    お書きいただいたコードをコピペして実行してみました。
    http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_listbox.html
    
    ユーザーフォームのコンボボックスの名前を cmb区分 の変更しただけです。
    
    cmb区分 には氏名のカタカナが相当していますので、コンボボックスのみを空欄にして実行しましたが
    エラーは発生しませんでした。
    テキストボックス1〜3のうち検索には1と3の値だけが使用されているみたいなので、
    テキストボックス1と3に入力して、コンボボックスはDeleteキーで削除して空欄にして実行。
    テキストボックス1だけに入力してコードを実行。
    テキストボックス3だけに入力してコードを実行。
    コンボボックスで1つだけ選択してコードを実行。
    いずれもエラーは出ませんでした。
    
    こちらはExcel2016で実行して確認してみましたが、データが異なるためか
    お書きのエラー↓が再現できませんでした。
    > その際に検索画面でコンビボックスやテキストボックスを空欄にして検索すると、「インデックスが有効範囲にありません。」と表示されてしまいます。
    
    ファイルをこちらに提供して頂くことが可能でしたらメールでお送りください。
    分かるかどうかは断言できませんが・・・・

引用返信/返信 削除キー/
■13302 / inTopicNo.7)  Re[6]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ なお Mail -(2018/09/25(Tue) 17:21:45)
    よろしければファイルのご確認お願いします。

    メールはどちらのアドレスに送付させてもらえればよろしでしょうか?

    どうぞよろしくお願いします。
引用返信/返信 削除キー/
■13303 / inTopicNo.8)  Re[7]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/09/25(Tue) 17:51:07)
引用返信/返信 削除キー/
■13304 / inTopicNo.9)  Re[8]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/09/26(Wed) 07:50:57)
    2018/09/27(Thu) 08:10:10 編集(投稿者)
    エラーの原因は
    myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 6).End(xlUp)).Value
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    と最終行を異なる列で求めており、シートの最終行がこの2つの列で異なっていたことでした。
    
    よって、
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    を
    lastRow = .Cells(Rows.Count, 6).End(xlUp).Row
    とすることでエラーの回避が可能と考えました。
    (このことはメールにて質問者へ回答しました)
    
    -------(追記)-------
    
    以下のように修正した方がよいと考え、HPも以下のように修正しました。
    '--- 2018/9/27修正しました
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    myData = .Range(.Cells(1, 1), .Cells(lastRow, 7)).Value
    

引用返信/返信 削除キー/
■13316 / inTopicNo.10)  Re[9]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ やーまん -(2018/10/23(Tue) 00:37:31)
    はじめまして、質問させていただきます。
    例の住所録データベースで、電話番号や年齢でも検索できるフォームを追加したいと思っています。
    どのように追記すればよいかわかりません。

    アドバイスをお願いします。
引用返信/返信 削除キー/
■13318 / inTopicNo.11)  Re[10]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/10/23(Tue) 08:36:29)
    検索するキーが多くなるのなら、コードを書くのも面倒(複雑)になりませんか?
    シートで直接、オートフィルターなどで検索したらいかがでしょうか?

引用返信/返信 削除キー/
■13319 / inTopicNo.12)  Re[11]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ やーまん -(2018/10/23(Tue) 09:37:53)
    検索性も含めて、ユーザーフォームで検索したいと考えております。
    どのようにやってもうまくいきません。
    よろしくお願いします。
引用返信/返信 削除キー/
■13321 / inTopicNo.13)  Re[12]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/10/23(Tue) 10:54:14)
    氏名、住所、郵便番号、都道府県、電話番号、年齢をキーとした例です。
    それぞれユーザーフォームにテキストボックスを追加しています。
    
    '検索を実行します。部分一致検索を行っています。
    Private Sub CommandButton1_Click()
    Dim lastRow As Long
    Dim myData, myData2(), myno
    Dim i As Long, j As Long, cn As Long
    
    Dim key1 As String, key2 As String, key3 As String, key4 As String, key5 As String, key6 As String
        '氏名
        If TextBox1.Value = "" Then key1 = "*" Else key1 = "*" & TextBox1.Value & "*"
        '住所
        If TextBox2.Value = "" Then key2 = "*" Else key2 = "*" & TextBox2.Value & "*"
        '郵便番号
        If TextBox3.Value = "" Then key3 = "*" Else key3 = "*" & TextBox3.Value & "*"
        '都道府県
        Dim ListNo As Long
          ListNo = ComboBox1.ListIndex
              If ListNo < 0 Then
                  key4 = "*"
              Else
                  key4 = ComboBox1.List(ListNo)
              End If
    
        '電話番号
        If TextBox4.Value = "" Then key5 = "*" Else key5 = "*" & TextBox4.Value & "*"
        '年齢
        If TextBox5.Value = "" Then key6 = "*" Else key6 = TextBox5.Value
    
    
    '検索するデータを配列 myData に格納しています。
    With Worksheets("Sheet1")
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    myData = .Range(.Cells(1, 1), .Cells(Rows.Count, 11).End(xlUp)).Value
    End With
    
    '配列 myData の中で検索で一致したデータを配列 myData2 に格納しています。
    ReDim myData2(1 To lastRow, 1 To 7)
    For i = LBound(myData) To UBound(myData)
          If myData(i, 2) Like key1 And myData(i, 7) Like key2 And myData(i, 5) Like key3 And myData(i, 6) Like key4 And myData(i, 11) Like key5 And myData(i, 10) Like key6 Then
    
    
    cn = cn + 1
    myData2(cn, 1) = myData(i, 1) '連番
    myData2(cn, 2) = myData(i, 2) '氏名
    myData2(cn, 3) = myData(i, 3) 'カタカナ
    myData2(cn, 4) = myData(i, 5) '郵便番号
    myData2(cn, 5) = myData(i, 7) '住所
    myData2(cn, 6) = myData(i, 11) '電話番号
    myData2(cn, 7) = myData(i, 10) '年齢
    End If
    Next i
    
    '検索で一致したデータをリストボックスに表示します。
    With ListBox1
    .ColumnCount = 7
    .ColumnWidths = "20;70;70;30;150;70;30"
    .List = myData2
    End With
    
    End Sub

引用返信/返信 削除キー/
■13324 / inTopicNo.14)  Re[13]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ やーまん -(2018/10/23(Tue) 17:09:13)
    ありがとうございました。早速やってみようと思います。
    ちなみになんですが、今回データの行を増やしてやってみようと思っているのですが、このままで問題ないのでしょうか?
    アドバイスをお願い致します。

引用返信/返信 削除キー/
■13325 / inTopicNo.15)  Re[14]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ よねさん -(2018/10/24(Wed) 07:11:41)
    データの行数の変化は問題ないと思います。
    コードは完全なものと思っていませんので、何らかの不具合もあり得ます。
    実情に合わせて改変してください。
    

引用返信/返信 削除キー/
■13332 / inTopicNo.16)  Re[15]: Excel(エクセル) VBA入門:検索結果をリストボックスに表示する
□投稿者/ やーまん -(2018/10/25(Thu) 17:03:51)
    ありがとうございました。
    試してみます。
引用返信/返信 削除キー/



トピック内ページ移動 / << 0 >>

このトピックに書きこむ

Pass/

HOME HELP 新規作成 新着記事 ツリー表示 トピック表示 検索 過去ログ

- Child Tree -