Office TANAKA - Excel VBA Tips[非表示のセルを検索する]

アクティブシートではない(開いていない)ワークシートを検索するには、どうしたらいいでしょう。ここでは下図のような表で考えてみます。

Sheet1のセルB3に入力した商品番号を、Sheet2のリストから検索して、見つかった単価をSheet1のセルC3に入力します。検索にはFindメソッドを使います。検索の対象範囲はSheet2のセル範囲A2:A21です。次のようなコードになりますね。

Sub Sample1()
    Dim FoundCell As Range
    Set FoundCell = Sheets("Sheet2").Range("A2:A21").Find(Range("B3"))
    If FoundCell Is Nothing Then
        Range("C3") = "Not Found"
    Else
        Range("C3") = FoundCell.Offset(0, 1)
    End If
End Sub

さて、ここからが問題です。それでは、Sheet2のリストがオートフィルタで絞り込まれていたとしたらどうでしょう。

検索したい"A-008"は、Sheet2のリストに入力されているのですが、Findメソッドは非表示のセルを検索できないんです。

これは、ワークシート上で手動操作したときも同じです。

これは、Excelの検索機能における仕様です。もちろん、VBAもその仕様に従います。

では、非表示になっているかもしれない表から、セルを検索するにはどうしたらいいでしょう。検索の前に、検索の対象範囲(ここではSheet2)がオートフィルタによって絞り込まれているかどうかを確認しますか？それで、もしオートフィルタによる絞り込みがされていたら、絞り込みを解除してから検索をして・・・まぁ、とりあえず、やってみましょうか。

ちなみに、任意のワークシートにオートフィルタが設定されていて、かつ絞り込まれているかどうかを判定するやり方が、悩まずにすぐ書けますか？きっと多くの方がネットを検索するんでしょうね。検索しなくてもいいように、当サイト内のリンクを貼っておきます。

オートフィルタの状況を調べる(VBA)

オートフィルタで絞り込まれているか判定する(ワークシート関数)

Sub Sample2()
    Dim FoundCell As Range
    With Sheets("Sheet2")
        If .AutoFilterMode Then             ''オートフィルタが設定されていたら
            If .AutoFilter.FilterMode Then  ''絞り込みがされていたら
                .Range("A1").AutoFilter     ''オートフィルタを解除する
            End If
        End If
        Set FoundCell = .Range("A2:A21").Find(Range("B3"))
        If FoundCell Is Nothing Then
            Range("C3") = "Not Found"
        Else
            Range("C3") = FoundCell.Offset(0, 1)
        End If
    End With
End Sub

オートフィルタが設定されると、AutoFilterModeプロパティがTrueを返します。しかしそれは、オートフィルタ矢印が表示されているかどうかです。オートフィルタによって絞り込みがされているかどうかは、AutoFilterオブジェクトのFilterModeプロパティで判定します。ただし、注意が必要です。FilterModeプロパティは、Excel 2007で追加されたプロパティですから、当然ですがExcel 2003まででは使えません。

それでも何とか工夫すれば、Excel 2003まででも絞り込みを判定することは可能です。ですが、待ってください。そんな苦労をしなくてもいいんです。セルの検索をFindメソッドだけに頼るから、どんどん難しくなっていくんです。Excelには、いろんな機能が実装されているのを思い出してください。

たとえば、ワークシート関数のVLOOKUP関数を使えば、この問題は解決です。

この関数ををVBAから利用します。

Sub Sample3()
    On Error Resume Next
    Range("C3") = WorksheetFunction. _
                    VLookup(Range("B3"), Sheets("Sheet2").Range("A2:B21"), 2, False)
    If Err > 0 Then Range("C3") = "Not Found"
End Sub

INDEX関数とMATCH関数を使う手もありますね。

Sub Sample4()
    Dim WF As Object
    On Error Resume Next
    Set WF = WorksheetFunction
    With Sheets("Sheet2")
        Range("C3") = WF.Index(.Range("B2:B21"), WF.Match(Range("B3"), .Range("A2:A21"), 0))
    End With
    If Err > 0 Then Range("C3") = "Not Found"
End Sub

WorksheetFunctionをオブジェクト変数WFに格納したのは、特に深い意味はありません。1行のコードが長すぎて、上の枠に入りきらないから短くしたかっただけです。

さらに、次のようなごく簡単なコードで実現することも可能です。

Sub Sample5()
    Dim i As Long, LastRow As Long
    With Sheets("Sheet2")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Range("C3") = "Not Found"
        For i = 2 To LastRow
            If Range("B3") = .Cells(i, 1) Then
                Range("C3") = .Cells(i, 2)
                Exit For
            End If
        Next i
    End With
End Sub

数千件のデータだったら、一瞬で終わりますので、ぜひお試しください。上記のコードに難しいテクニックはありません。必要なのは、VBAの基礎と柔軟な発想だけです。