Office TANAKA - Excel VBA Tips[すべて検索する]

見つかったセルのアドレスをUserFormで表示する

次のようなUserFormで表示してみましょう。

リストボックスを選択すると、ワークシート上で該当するセルにアクティブセルを移動します。

すべてを検索するところは、前ページの「見つかったセルを別のシートにコピーする」と同じです。別のシートにコピーする代わりに、UserFormのリストボックスに、セルのアドレスと値を登録します。そして、すべての検索が終了したら、UserFormを表示してやります。

Sub Sample8()
    Dim FoundCell As Range, FirstCell As Range
    Set FoundCell = Cells.Find(What:="田中")
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
        Exit Sub
    Else
        Set FirstCell = FoundCell
        リストボックスにアドレスと値を登録
    End If
    Do
        Set FoundCell = Cells.FindNext(FoundCell)
        If FoundCell.Address = FirstCell.Address Then
            Exit Do
        Else
            リストボックスにアドレスと値を登録
        End If
    Loop
    UserFormを表示
End Sub

リストボックスに登録するのは、検索で見つかったセルのアドレスと、そのセルに入力されている値です。それぞれ「FoundCell.Address」と「FoundCell.Value」で取得できます。

リストボックスにデータを登録するには

ListBox1.AddItem 登録したいデータ

とします。アドレスと値がくっついては見にくいので、間にタブ(vbTab)を入れましょうか。

ListBox1.AddItem FoundCell.Address & vbTab & FoundCell.Value

となります。

Sub Sample8()
    Dim FoundCell As Range, FirstCell As Range
    Set FoundCell = Cells.Find(What:="田中")
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
        Exit Sub
    Else
        Set FirstCell = FoundCell
        UserForm1.ListBox1.AddItem FoundCell.Address & vbTab & FoundCell.Value
    End If
    Do
        Set FoundCell = Cells.FindNext(FoundCell)
        If FoundCell.Address = FirstCell.Address Then
            Exit Do
        Else
            UserForm1.ListBox1.AddItem FoundCell.Address & vbTab & FoundCell.Value
        End If
    Loop
    UserFormを表示
End Sub

UserFormを表示するのはShowメソッドですが、ここはひとつ[検索と置換]ダイアログボックスのように、UserFormを表示している間も、ワークシートを操作できるようにしましょう。それには、UserFormをモードレスで表示します。UserFormをモードレスで表示するには、Showメソッドの引数に、定数vbModelessを指定します。

Sub Sample8()
    Dim FoundCell As Range, FirstCell As Range
    Set FoundCell = Cells.Find(What:="田中")
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
        Exit Sub
    Else
        Set FirstCell = FoundCell
        UserForm1.ListBox1.AddItem FoundCell.Address & vbTab & FoundCell.Value
    End If
    Do
        Set FoundCell = Cells.FindNext(FoundCell)
        If FoundCell.Address = FirstCell.Address Then
            Exit Do
        Else
            UserForm1.ListBox1.AddItem FoundCell.Address & vbTab & FoundCell.Value
        End If
    Loop
    UserForm1.Show vbModeless
End Sub

これで、標準モジュール側のマクロは完成です。あとは、UserForm側のマクロですね。

まず、簡単なところで[閉じる]ボタンからいきましょうか。[閉じる]ボタンをダブルクリックして、次のプロシージャを記述します。

Private Sub CommandButton1_Click()
    Unload Me
End Sub

次に、リストボックスでデータを選択したとき、そのセルにアクティブセルを移動する処理です。リストボックスをダブルクリックすると、次のプロシージャが挿入されます。

Private Sub ListBox1_Click()

End Sub

このListBox1_Clickプロシージャは、リストボックスのデータがクリックされたときに発生します。また、マウスのクリックだけでなく、矢印キーなどでデータを選択しても発生します。
さて、リストボックスで選択されているデータはValueプロパティで取得できます。

ListBox1.Value

ここには「セルのアドレス<Tabコード>セルの値」という形式で登録しました。アクティブセルを移動するのに必要なのは、Tabコードの左側にある「セルのアドレス」ですから、これを抜き出さなければなりません。こんなときに便利なのがSplit関数です。

Split関数は、文字列を指定した文字で区切り、分割されたそれぞれのデータを配列形式で返す関数です。CSVファイルのデータをカンマで区切るのをイメージしてください。たとえば「tanaka,suzuki,yamada」という文字列が変数bufに格納されていたとします。Split関数を使って、この変数bufをカンマで区切るには、次のようにします。

Split(buf, ",")

結果は、次のような配列形式になります。

(0) tanaka
(1) suzuki
(2) yamada

今回は、ListBox1.Valueで取得できる「アドレス Tabコード値」を「Tabコード」で分割します。Split関数が返す配列の(0)が左側のアドレスになります。

Split(ListBox1.Value, vbTab)(0)

結果はアドレスですから、これを変数に格納して、Rangeに使いましょう。

Private Sub ListBox1_Click()
    Dim Target As String
    Target = Split(ListBox1.Value, vbTab)(0)
    Range(Target).Select
End Sub