複数シートを検索した結果をリストボックスにすべて表示させたい

Question

前提・実現したいこと

VBA超初心者です。
こんなものがあったら楽だろうと思い、調べながらどうにかユーザーフォームから入力→転記、IDから検索、機器№から検索、検索結果をリストボックスに表示させ、選択した内容をユーザーフォームに表示をして情報を更新できるようなものを作成しました。（OA機器の管理表です）

検索するシートが2枚になっても同じようなことができるようにしたいのですが、結果が2枚目のものしか表示されなく、色々と検索をして似たようなものを試したりしたのですが、どうしてもわかりません。

どのようにしたら？何を使ったらできるのか？取っ掛かりみたいなものでもアドバイスを頂けたら勉強しながら作っていこうと思っています。このようなところに投稿するのも初めてで、慣れないのですがよろしくお願いします。

発生している問題・エラーメッセージ

検索結果は出てくるのですが、2枚目のものしか出てきません。

該当のソースコード

Private Sub 検索_Click()

Dim　i As Long, n As Long
Dim Lrow As Long
Dim SH As Worksheet
Dim mydata() As Variant
Dim mydata2() As Variant

For Each SH In Worksheets(Array("sheetA","sheetB"))
 Lrow = SH.Cells(SH.Rows.Count,1).End(xlUp).Row
 mydata = SH.Range(SH.Cells(1,1),SH.Cells(Lrow,14)).Value
 
  ReDim mydata2(1 To Lrow, 1 To 14)

    For i = LBound(mydata) To Ubound(mydata)
    
     If mydata(i,3) Like "*" & TextBox1.Value & "*" Then

      n = n + 1
      
       For x = 1 To 6
  
        mydata2(n , x) = mydata(i , x)

      Next x
    End IF
   Next i
 Next SH
 
With ListBox1
.ColumnCount = 6
.Columnwidth = "30;40;80;80;110;80"
.List = myData2
End With

End Sub

試したこと

ここ1週間色々と検索をしたのですが、どうしたら良いか全く手詰まりの状態です。Worksheets.Countにしてみたりなど、色々試してリストボックスに反映はされるのですが、SheetB の検索しか表示されません。

補足情報（FW/ツールのバージョンなど）

Office365

Answer 1

他の方も指摘してますが、動的配列は最後の次元しか増やすことができないので、行列を入れ替えるなどが必要になり面倒です。
動的配列に1件ずつ追加していくなどとせずに、リストボックスに直接1件ずつ追加していくのがシンプルだと思います。
リストボックスならAddItemで行追加できますので。

Private Sub 検索_Click()

    Dim i As Long
    Dim Lrow As Long
    Dim SH As Worksheet

    With ListBox1
        .ColumnCount = 6
        .ColumnWidth = "30;40;80;80;110;80"

        For Each SH In Worksheets(Array("sheetA", "sheetB"))
            Lrow = SH.Cells(SH.Rows.Count, 1).End(xlUp).Row
            mydata = SH.Range(SH.Cells(1, 1), SH.Cells(Lrow, 14)).Value

            For i = LBound(mydata) To UBound(mydata)

                If mydata(i, 3) Like "*" & TextBox1.Value & "*" Then

                    n = n + 1
                    .AddItem ""
                    For x = 1 To 6
                        .List(.ListCount - 1, x - 1) = mydata(i, x)
                    Next x
                End If
            Next i
        Next SH
    End With

End Sub

Comments

[candycandy]

アドバイスありがとうございます。教えていただきましたコードを一通り自分で書いて試してみました。感動しました。思わず、「おー！！」と声をあげてしまいました。と同時に、どこかのサイトに初心者は知識がないくせに理想が高いということが書いてあり、とても恥ずかしくなりました。
みなさんから頂いたアドバイスも少しずつ調べつつ、どんな風にしたらよいか？言葉の意味から勉強をしたりしています。Hatena19様からのコードも書きつつ、考えたり調べたりさせていただいております。
色々な書き方がありとても勉強になりますし、3名の方のコメントから得られることはたくさんありそうです。
まだまだ、いろんなサイトに載っているコードを真似するしかできないですが、様々な本の1ページ目から読むよりも、自分がこういうものを作ってみたいと思い、調べながらでもやることによって、知識になっていくと思っていますし、少しでも身につけば良いと思っております。
ぎりぎりまでは調べまくりますが、またどうしようもなくなりましたら、質問させていただこうと思います。ありがとうございました。

Answer 2

Redim mydata2(1 To Lrow, 1 To 14)が原因と思われます。
Redimは配列の個数を変える時に中身をリセットしてしまうので、中身を残したまま配列数を変えたい場合にはRedim Preserveを使います。

しかしRedim Preserveは配列の一番最後の要素数（今回で言う1 To 14の部分）しか変えられないため、今回の場合はRedim Preserve mydata2(1 To 14, 1 To Lrow)として、あとで行列を入れ替えるなどの工夫が必要になります。

また、今のままだと2枚目のシートに移った際に配列数を2枚目のLrowにしてしまうため、個人的には値を追加する度にRedim Preserve mydata2(1 To 14, Ubound(mydata2, 2)+1)としていく方が簡単かなと思います。

Comments

[candycandy]

早々にアドバイス頂き、ありがとうございました。配列、難しいです。Redim, Redim　Preservenについて、昨夜色々なサイトや動画を見まくりました。行や列を入れ替えるためにはどうしたらいいか？なども調べてみました。理解が浅く、また知らないことが沢山ありとても勉強になります。すぐに　できました　とはご報告できないですが、じっくり勉強したいと思います。

Answer 3

mydataとmydata2のRow数値をn = n + 1で加算していますが初期化するタイミングがありません。
サンプルプログラムでは2枚目のsheet処理時にエラーが発生し動作がとまります。
また、sheet遷移時に毎回ReDimを行っているため中身のデータが全てリセットされています。
With ListBox1以下に動くタイミングは全sheetの処理が終わった後になるため、正常に動作しても最後のデータのみ残っている状態です。
collectionのadd操作のような形をRedimによって疑似的に再現するのは管理が難しくてただ面倒なので1sheetずつに静的配列を作成して表示時につなぎ合わせる形の方が楽ですよ。

Comments

[candycandy]

アドバイス有難うございます。先ほどほかの方にもコメントいたしましたが、ものすごく勉強になります。昨夜から色々なサイトや動画を見ましたが、実際に書くとなるとなかなかむずかしく、1シートずつに静的配列を作成して表示をつなぎ合わせるという部分を今少しずつやっておりました。時間がかかると思いますが、色々と試したいと思います。

[退会済みユーザー]

結果をつなぎ合わせるには結果用シートを新規に作成しておいてCells(Rows.Count, 1).End(xlUp).RowでRow位置を確認しながら+1して順次貼り付けていく形が楽ですね。
最後に必要な個所に貼り付けます。
処理速度との兼ね合いもあるのでどこまで配列内でやるか考える必要はあります。
（セルを触れば触るほど遅くはなります）
また検索用シートが１枚で正常動作するのであれば、結果ではなく処理予定データを先に処理しやすい形で保存するのもいいです。
新規作成シートは毎回削除しておけば問題ありません。
（エラー回避的には動作開始時に存在確認して削除→作成だと途中で止まってもOK）
データ作成時は処理よりも入力の都合が優先されますし、他人が作ったプログラムで吐き出されるデータは結局自分で整えてやらないと駄目なパターンも多いのでそういった方面での解決もあると思います。