Excel VBAのRange.Findメソッドは、列幅が足りず#表示になっているセルの値を検索できない

いつものごとく仕事をサボって仕事の効率化のためにExcel VBAと戯れていた私。
本日、掲題の現象に出くわしたので、対策も含めてメモを残しておく。

現象の再現方法

例として、下図のような表とマクロを用意する。
「探したい数字」に数値を入れてマクロを実行すると、その値がどこのセルに入っているかをダイアログボックスに表示する。

Fig.1 サンプルのワークシート

Sub FindInValues()

    Dim findCell As Range, target As Variant
    
    target = Range("D2").Value

    Set findCell = Columns(2).Find(What:=target _
     , After:=Cells(1, 2) _
     , LookIn:=xlValues _
     , LookAt:=xlWhole _
     , SearchOrder:=xlByRows _
     , SearchDirection:=xlNext _
     , MatchCase:=False _
     , MatchByte:=False _
     , SearchFormat:=False)

    If Not findCell Is Nothing Then
        MsgBox target & "は" & findCell.Address & "にありました。"
    Else
        MsgBox target & "は見つかりませんでした。"
    End If

End Sub

Fig.2 マクロ"FindInValues"を実行すると結果が表示される

では、B列の幅を縮めて、探したい値が入っているセルが #### という表示になっている状態でマクロを実行するとどうなるか。

Fig.3 B5の値は20000なのに、マクロを実行すると、「見つからない」という結果に！

これはひどい。どうしてくれるんだMicrosoft。

これは実はマクロにかぎらず、Excel標準の検索ダイアログでも同じだ。

Fig.4 Excelの検索ダイアログを使っても、同様に"20000"を見つけられない

検証1. 検索方法を変えてみる

先のマクロは、Findメソッドの引数に"LookIn:=xlValues"を与えていた。
これは、Excelの検索機能でいうところの「検索対象：値」を意味する。
これを「検索対象：数式」にすると、表示が #### になっていてもヒットするようになる。

Fig.5 検索対象を数式にすると、表示が # でも検索にヒット

 同様のことをマクロで実装するなら、Findメソッドの引数を"LookIn:=xlFormulas"に変更すればよい。

Sub FindInFormulas()

    Dim findCell As Range, target As Variant
    
    target = Range("D2").Value

    Set findCell = Columns(2).Find(What:=target _
     , After:=Cells(1, 2) _
     , LookIn:=xlFormulas _
     , LookAt:=xlWhole _
     , SearchOrder:=xlByRows _
     , SearchDirection:=xlNext _
     , MatchCase:=False _
     , MatchByte:=False _
     , SearchFormat:=False)

    If Not findCell Is Nothing Then
        MsgBox target & "は" & findCell.Address & "にありました。"
    Else
        MsgBox target & "は見つかりませんでした。"
    End If

End Sub

Fig.6 検索対象を数式にすると、無事検索できた！

検証1. の問題点

これで万事解決かというと、実はそんなことはない。
セルに値が直接入力されている場合はこの方法で問題ないが、セルに計算式が入っている場合は「検索対象：数式」ではうまく検索できない（あたりまえ）。
試しに、B6セルに"=5000+5000"という数式を入れた状態で10000を検索しても、検索にはヒットしない。

Fig.7 検索対象を数式にすると、10000が検索にヒットしない

Fig.8 検証1のマクロでも同様にヒットせず

検証2. 列幅を調整し、値がちゃんと表示されるようにする

検証1は使いものにならないので、アプローチを変えるしかない。
セルの表示が # になっているのが問題なのであれば、列幅を調整して値がちゃんと表示されるようにしてあげればよい。
下記がサンプルコード。

Sub FindInValues2()

    Dim findCell As Range, target As Variant
    Dim targetTable As Range, c As Range
    
    'セルが"#"表示になっていたら列幅を自動調整する
    Set targetTable = Cells(1, 2).CurrentRegion
    For Each c In targetTable
        If Not IsError(c.Value) Then
            If c.Value <> c.Text And Left(c.Text, 1) = "#" Then
                Debug.Print c.Address(False, False) & "の列幅を自動調整します"
                c.Columns.AutoFit
            End If
        End If
    Next c
    
    target = Range("D2").Value

    Set findCell = Columns(2).Find(What:=target _
     , After:=Cells(1, 2) _
     , LookIn:=xlValues _
     , LookAt:=xlWhole _
     , SearchOrder:=xlByRows _
     , SearchDirection:=xlNext _
     , MatchCase:=False _
     , MatchByte:=False _
     , SearchFormat:=False)

    If Not findCell Is Nothing Then
        MsgBox target & "は" & findCell.Address & "にありました。"
    Else
        MsgBox target & "は見つかりませんでした。"
    End If

End Sub

下図の状態で、このマクロを実行してみる。

Fig.9 表示が#になっている状態でFindInValues2を実行

Fig.10 列幅が自動調整され、正しく検索される

うまくいった。

結論

Range.Findメソッドで検索を行うマクロを組むときは、事前に列幅を調整する機能を実装することが必須である。
めんどくさいなあ。

参考

本エントリの執筆にあたりExcel VBA を学ぶなら moug モーグ | 即効テクニック | ＃＃＃表示かどうかを判定するを参考にさせていただきました。