VBA Findメソッドで日付を検索したいがうまくいかない

Question

VBAのFindメソッドを使って日付を検索して、その日付が入力されている行を取得したいのですが、Findメソッドの挙動が理解できなくてうまくいかず、困っています。

エクセルはExcel 2013の64bit版を使用しています。

具体的な状況は以下の通りです。

セルA1に"2021/11/5"という文字列が入力されています
セルA1の書式設定はユーザー定義で「yyyy"年"m"月"d"日"」としています
セルA1には"2021年11月5日"と表示されています。

この状態で以下のコードを実行します。

Sub find()
    
    Dim a As Variant
    Set a = Worksheets("Sheet1").Range("A1").find(what:="2021年11月5日", LookIn:=xlValue)
    
    MsgBox a.Row

End Sub

コードの意図は、セル範囲A1から"2021年11月5日"という文字列を検索して、検索に一致したセルの行番号をMsgBoxに返したいのですが、エラーがでます。

実行時エラー"91":
オブジェクト変数または Withブロック変数が設定されていません

デバックモードでaにマウスカーソルを合わせると"a = Nothing"と表示されます。

おそらくこのエラーはセル範囲A1から"2021年11月5日"という文字列が発見できていないことが原因と考えました。

日付型の検索はいろいろ面倒ということなので以下のコード書いて検証しました。
セルA1には"a"という文字列が入力してあります。
セルA1の書式設定は標準です。

Sub find2()

    Dim a As Variant
    Set a = Worksheets("Sheet1").Range("A1").find(what:="a", LookIn:=xlValues)
  
    MsgBox a.Row

End Sub

すると以下のエラーがでます。

実行時エラー"9":
インデックスが有効範囲にありません。

デバックモードでaにカーソルをあわせると"a = Empty 値"と表示されます。
NotingとEmptyが別の状態であることはなんとなく認知していますが、なぜ上記二つのコードでエラーの内容が変わり、二つのコードとも検索できないのか理解できません。

おそらくFindメソッドの使い方を間違っているのではないかと推測しています。
最終的にはセルA1に日付が入力されていて、A1以下のセル(セル間の間隔はランダム)にA1+1 An+1...というように日付が連続で入力されているシートで任意の日付を検索したいです。もともとはFindメソッドを使って文字列や日付の検索を行なうところまではできていて、A1+1のように日付型で数式が入力されているセルの検索ができなくて困っている状況だったのですが、試行錯誤するうちに文字列の検索のやりかたさえわからなくなりました。

なぜ上記のようなコードで検索できないのか教えてください。

追記(2021/11/8 10:40)

一歩前進しました。
文字列が検索できなかった原因が判明し、文字列については検索することができました
文字列が検索できなかったのはセル結合を行なっていたことが原因でした。

具体的にはセルA1からC1までを結合していました。この状態で

Set a = Worksheets("Sheet1").Range("A1").find(what:="a", LookIn:=xlValues)

とするとA1にaと入力されていても検索に引っかからないようです。

Set a = Worksheets("Sheet1").Range("A1:C1").find(what:="a", LookIn:=xlValues)

とすることで検索することができました。
検索したい値が入力されているセルが結合されている場合、結合範囲全体を検索範囲にしないと検索に引っかからないようです。
なので同じような状態の人でセルの結合を行なっている場合は検索範囲を結合範囲と同じにしてみてください。
検索範囲をcellsで指定した場合どういう挙動になるのかは検証していません。

あとは日付型の検索と、日付型を数式で処理しているセルの検索方法がわからないので、引き続き回答を募集します。
進捗があればまた追記をおこないます。

結論(2021/11/8 11:08)

Findメソッドで日付を検索することは諦めてMatch関数を使用することで日付の検索を行なうことができました。
「VBA Find 検索できない」などで検索してシリアル値で検索しなければいけないことやMatch関数を使用する方法のほうが簡単であることは認知していましたが、なかなかうまくいきませんでした。
結局ベスト回答に選ばせていただいたご回答の下記のコードを参考にすることで目的を達成することができました。

Dim a As Long
        a = Application.Match(CLng(CDate("2021年11月7日")), Range("A:A"), 0)
    MsgBox a

Comments

[jinoji]

まずは落ち着いて、xlValuesがxlValueになっているのを直すとよいと思います。

[tatatace]

御指摘ありがとうございます。
タイプミスです。気がついていませんでした。

[jinoji]

インデックスが有効範囲にありません。のエラーは、おそらくそのタイプミスが原因だと思われます。
また、もともとのコードがうまくいかないのは、ご自身で突き止めた通りセル結合が原因で、
日付をFindで検索すること自体は、問題なくできるということですかね。

[tatatace]

おっしゃる通りですね。
エラーの原因までわかってすっきりしました。ありがとうございます。

Answer 1

Sub find()

    Dim a As Variant
    Set a = Worksheets("Sheet1").Range("A:C").find(what:="2021年11月5日", LookIn:=xlValues)

    If Not a Is Nothing Then
        MsgBox a.Row
    Else
        MsgBox "Not Found"
    End If

End Sub

Comments

[tatatace]

IF文でFindがNothingを返した場合の処理をMsgBox "Not Found"と指定することでエラーを回避することができるコードですね。参考になります。
タイプミスにも気をつけます。
ありがとうございます。

Answer 2

まず、これに関して、

NotingとEmptyが別の状態であることはなんとなく認知していますが、なぜ上記二つのコードでエラーの内容が変わり、二つのコードとも検索できないのか理解できません。

Find は見つからなければ、Noting が返ります。
Empty が返るということは、Findの実行に失敗しているということです。（存在しないシート名とか）
提示のコードは実際のコードをコピーしたものでしょうか。
当方で試したところでは、Noting でした。

A1+1のように日付型で数式が入力されているセルの検索ができなくて困っている状況だったのですが、試行錯誤するうちに文字列の検索のやりかたさえわからなくなりました。

Findメソッドで日付を検索するといろいろ難しいというのは検索すればいろいろヒットすると思います。

Excel VBA を学ぶなら moug モーグ | 即効テクニック | 日付を検索する

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

セルA1に"2021/11/5"という文字列が入力されています

セルA1の書式設定はユーザー定義で「yyyy"年"m"月"d"日"」としています
セルA1には"2021年11月5日"と表示されています。

これはセルには文字列ではなくて、シリアル値が格納されている状態です。
Findメソッドはいろいろ難しいので、シリアル値が格納されているのなら、私ならMatch関数で検索します。

Dim a as Long
a = Application.Match(CLng(CDate("2021年11月5日")),Range("A:A") ,0)
MsgBox a

Comments

[tatatace]

シリアル値で検索する、Match関数を利用する方法もあるというのはこの問題が起きた当初から認知していたのですが、うまく検索することができていなくて、これからその旨を追記するところでしたが、このご回答のコードを参考にすることでMatch関数で検索を行なうことができました。

ありがとうございました。