Office TANAKA - Excel VBA Tips[日付を検索する]

検索に「ときどき」失敗するケース

下図のようなデータを例にしましょう。

A列に日付が入力されています。ここから「1月24日」を検索して、見つかったセル右隣の担当者を返すマクロを考えてみましょう。次のような感じでしょうか。

Sub Sample3()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="2010/1/24")
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

ところが、まったく同じデータに対して、まったく同じマクロを実行したとしても、検索に失敗するケースがあります。

ワークシート内を手動で検索する[検索と置換]ダイアログボックスには、さまざまなオプションが存在します。[検索対象]では「数式」「値」「コメント」を区別して検索することが可能です。

この[検索対象]オプションですが、先のコードでは何も指定していませんでした。

Sub Sample3()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="2010/1/24")    ''オプションを何も指定していない
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

Findメソッドで検索を行うとき、省略可能なオプションを省略すると前回検索されたときの状況を引き継ぎます。これは、VBAのFindメソッドに限った話ではありません。手動操作による[検索と置換]ダイアログボックスの状況が、Findメソッドにも影響します。先の例で、検索に成功したのはたまたま前回の検索で[検索対象]が「値」になっていたからです。その後、手動操作で[検索対象]が「数式」による検索を実行すると、次に同じマクロを実行しても失敗してしまうわけです。

ところで、この[検索対象]って何のオプションなのでしょう。何となく想像はつきますが、念のためにヘルプで調べてみます。

はぁ？って感じですね。「特定の詳細を含むデータ」って何だよ？てか、日本語になってないじゃん・・・みたいな。
日本語のローカライズといえば、何かと単語や用語にばかり注目しますが、そもそも普通の日本語で書きましょうよってレベルですね。まずは、文章として成立させて欲しいです。

「コメント」を選択すると、セルに挿入したコメント内を検索できます。「数式」と「値」で検索結果が異なるのは、次のようなケースです。「数式」は、セルに入力されている数式や値を検索します。下図の表で「60」が入力されているセルは、どこにもありませんので、「数式」で"60"は見つかりません。

セルB6に入力されている数式には"SUM"という文字が含まれていますから、「数式」で"SUM"を見つけることは可能です。

「値」とは、いわばセルに表示されているデータです。セルB6にはSUM関数が入力されていますが、セルには計算結果である"60"が表示されています。なので、「値」で"60"を見つけることができます。

しかし、どのセルにも"SUM"という文字列は表示されていません。なので「値」で"SUM"は検索できません。

この[検索対象]を指定するのが、Findメソッドの引数LookInです。引数LookInには、どんな値を指定できるのでしょう。これもヘルプで調べてみます。

ため息しか出ませんね。下の[LookAt]には「xlWholeまたはxlPartを指定します」と定数が書かれているのに、[LookIn]の方は「情報の種類を指定します」って・・・情報って何の情報ですか？種類って何ですか？どうやって指定すればいいんですか？TrueとFalseですか？1と2ですか？何かの文字列ですか？・・・と、ここからは何も得られません。ある意味、軽いイジメかもしれません。

引数LookInに指定できるのは、次の3つの定数です。

定数名	定数の値	内容
xlComments	-4144	コメント
xlFormulas	-4123	数式
xlValues	-4163	値

先のコードで、引数LookInの違いを確認しましょう。

Sub Sample3()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="2010/1/24", LookIn:=xlFormulas)
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

「数式(xlFormulas)」は、セルに入力されている数式やデータを検索します。セルA10には、実際には「40202」という数値(シリアル値)が入力されているのですから、見つからないのも何となく納得はできます。セルA10には「2010/1/24」が表示されているのですから「値(xlValues)」なら検索できるのでしょう。

Sub Sample3()
    Dim FoundCell As Range
    Set FoundCell = Range("A:A").Find(What:="2010/1/24", LookIn:=xlValues)
    If FoundCell Is Nothing Then
        MsgBox "見つかりません"
    Else
        MsgBox FoundCell.Offset(0, 1)
    End If
End Sub

ここまでの解説はよろしいですか。

Findメソッドの引数を省略すると、前回の検索で指定されたオプションが適用される
前回の検索で指定されたオプションは、手動操作とVBAを問わない
[検索対象]の「数式」は、セルに入力されている数式や値を検索できる
[検索対象]の「値」は、セルに表示されているデータを検索できる

特に、最後の2つです。実際の操作は、SUM関数を例にして、上記で解説しました。では、次のような実験をしてみましょう。

A列とB列を、そっくりD列とE列にコピーしました。ただし、A列とD列の日付データは、次のように入力をし直しました。まずA列では、セルA2に「2010/1/6」を入力して、フィルハンドルをセルA27までドラッグしました。これによって、A列では、すべてのセルにシリアル値が入力されています。

一方D列は、セルD2にだけ「2010/1/6」という日付を入力し、セルD3以降は数式によって翌日を計算しています。先の「SUM関数」と「60」の解説を思い出してください。今回のD列には、どこにも「2010/1/24」という日付(シリアル値)は入力されていません。このA列とD列に対して「数式(xlFormulas)」と「値(xlValues)」の検索を、それぞれ試してみましょう。

Sub Sample4()
    Dim FoundCell As Range, msg As String
    msg = "【A列】" & vbCrLf
    Set FoundCell = Range("A:A").Find(What:="2010/1/24", LookIn:=xlFormulas)
    If FoundCell Is Nothing Then
        msg = msg & "xlFormulas：見つかりません" & vbCrLf
    Else
        msg = msg & "xlFormulas：" & FoundCell.Offset(0, 1) & vbCrLf
    End If
    Set FoundCell = Range("A:A").Find(What:="2010/1/24", LookIn:=xlValues)
    If FoundCell Is Nothing Then
        msg = msg & "xlValues：見つかりません" & vbCrLf
    Else
        msg = msg & "xlValues：" & FoundCell.Offset(0, 1) & vbCrLf
    End If
    msg = msg & "【D列】" & vbCrLf
    Set FoundCell = Range("D:D").Find(What:="2010/1/24", LookIn:=xlFormulas)
    If FoundCell Is Nothing Then
        msg = msg & "xlFormulas：見つかりません" & vbCrLf
    Else
        msg = msg & "xlFormulas：" & FoundCell.Offset(0, 1) & vbCrLf
    End If
    Set FoundCell = Range("D:D").Find(What:="2010/1/24", LookIn:=xlValues)
    If FoundCell Is Nothing Then
        msg = msg & "xlValues：見つかりません" & vbCrLf
    Else
        msg = msg & "xlValues：" & FoundCell.Offset(0, 1) & vbCrLf
    End If
    MsgBox msg
End Sub

引数LookInにxlFormulasまたはxlValuesを指定すれば、セルに入力されているデータと、セルに表示されているデータの検索を区別できます。セルにシリアル値が入力されているのか、それとも数式によって計算された結果の日付かも、同じように引数LookInで切り替えられるはずです。しかし、どちらにせよxlValuesを指定しないと検索できません。ちょっと不思議ですね。ちなみに、手動操作では、また違った結果になります。

日付の検索は、いろいろと頭を悩ますようなことが多いです。対処法は・・・「こういうことがある」と認識しておくことでしょうか。「日付の検索は一筋縄ではいかないんだ」と。それを実感する事例を、次のページでご紹介します。

ほかにもある「ときどき」失敗するケース >> 次ページ