EXCEL VBA 時間の検索 上手くいかない…

素人です。お願いします。
交代制勤務のシフト表（A列　シフト名、Ｂ列　始業時間、Ｃ列　終業時間）から、Ｂ列の時間を検索してその時間のシフト名を調べるマクロを作りたいのですが、上手くいきません。
A列のシフト名の検索は出来たのですが、時間の検索だとエラーがでてよくわかりません。
ちなみにシフト名の検索はこんな感じでうまくいきました。
userformを使いtextboxに検索したいシフトを入力し、該当があればそのセルを選択するようにしました。これを時間（Ｂ列）の検索に変えたいのですが、上手くいかないです。

With ActiveWorkbook.Worksheets("シフトコード")
.Range("A1") = TextBox1.Text
End With

Dim Rng As Range

For Each Rng In Range("A4:A1500")
If Rng.Value = Range("a1") Then
Rng.Activate
Exit Sub
End If
Next

MsgBox "該当なし"

どうすれば時間を検索できるか教えて下さい。
宜しくお願いします。

質問者からの補足コメント

• 

  色々な角度から回答をいただきありがとうございました。いろいろ試してみましたが、なかなかうまく検索してくれません。
  a/コード　ｂ/実動　ｃ/始業　ｄ/終業
  WA84　　6:00 　4:00 11:00　　　　　
  AA84 　　6:00 　5:00 12:00
  BA2 　　6:00 　6:00 13:00
  CG2 　　6:00 　7:30 14:30
  KA2 　　6:00 　14:00 21:00
  LA1 　　6:00 　15:00 22:00
  LG2 　　6:00 　15:30 22:30
  NA1 　　6:00 　17:00 0:00
  RA4 　　6:00 　21:00 4:00
  TA1 　　6:00 　23:00 6:00
  BG1 　　6:00 　6:30 13:30
  CA1 　　6:00 　7:00 14:00
  CD2 　　6:00 　7:15 14:15
  
  続く

    補足日時：2016/01/01 13:28

• 

  表は実動6時間の部分ですが、6：00を検索すると、先頭のWA84ではなくBG1の6：00を選んでしまいます。
  With ActiveWorkbook.Worksheets("シフトコード")
  .Range("L4") = TextBox1.Value
  End With
  Dim ret As Variant
  Dim myTime As Double
  With ActiveSheet
  myTime = .Range("L4").Value
  ret = Application.Match(myTime, .Range("B1:B1500"), 0)
  If Not IsError(ret) Then
  .Cells(ret, "B").Select
  Else
  各時間とも先頭を選んだり、途中を選んだりとまちまちです。
  書式？形式？が違うのでしょうか？本当に素人でスミマセン。

    補足日時：2016/01/01 13:41

No.10 回答者： 30246kiku 回答日時： 2016/01/04 21:17

＃４です

ご迷惑をおかけしました

私より先に回答つける方がいるかと思いましたが･･･

解決されていましたでしょうか？
解決に結びつくかわかりませんが、以下でデータを確認してみませんか
★部分を 6:00 の範囲に設定して実行すると、
値の重複を排除しながら違う値が出現した初めのセル番地を覚えておき、最後に出力します
（結果はイミディエイトウィンドウに）

Public Sub testData()
　　Dim dic As Object
　　Dim r As Range
　　Dim v As Variant

　　Set dic = CreateObject("Scripting.Dictionary")

　　For Each r In Range("B1:B1500") ' ★
　　　　v = r.Value
　　　　If (v <> "") Then
　　　　　　If (Not dic.Exists(v)) Then
　　　　　　　　dic(v) = r.Address(False, False)
　　　　　　End If
　　　　End If
　　Next

　　Debug.Print ">> 値の個数"; dic.Count
　　On Error Resume Next
　　For Each v In dic.Keys
　　　　Debug.Print dic(v), v,
　　　　Debug.Print CDate(v),
　　　　Debug.Print
　　Next

　　Set dic = Nothing
End Sub

ここで、値の個数が１個の場合、解決方法等まったくわかりません
ただ、複数個の場合、

> With ActiveWorkbook.Worksheets("シフトコード") ' ▲▲
> 　　.Range("L4") = TextBox1.Value
> End With
> Dim ret As Variant
> Dim myTime As Double
> With ActiveSheet ' ▲▲
> 　　myTime = .Range("L4").Value

より、Range("L4").Value　が異なるシートを指していて、
たまたま異なる値が入っていて、たまたま一致したとか？

Find で出来るそうなので Match → Find に変えてやってみるとか？

補足に提示されたデータを貼り付けたものでは
　　myTime = #6:00:00 AM#　後の Match で
WA84 の所が選択されましたが、
＃４でのシートでは、エラー 2042 で確認できませんでした（ 2007 ）
こう記述すると、おかしいように思えるかもしれませんが、
＃４のシートには 6:00 は無いので･･･


方法を提示された方の補足に記述されると、早い回答が得られるかも
回答者の多くは、
> そのスレが解決するということが第一
と考えられている人が多いのか･･･と期待はしています


余談）

＃４での　Format(XXX,"yyyymmddhhnnss")　で変換してから･･･
これ、Access でも比較できない時があって、対処した方法だったので紹介してみました
ブログにチョット書いてましたが、URL 記述できないので･･･

Excel って賢いのですね

失礼しました

No.6 回答者： WindFaller 回答日時： 2015/12/29 18:56

私は、

》#2の内容は、マクロの教科書などに載っている、基本中の基本なのです
というのは、間違いないはずです。どこのと言われると困りますが、一度はやったことがあるものだと思います。それ自体を否定すると、全体がおかしなことになってしまいます。

モーグの著者は、
》マクロで日付を検索するときに最も大事なことは『いろいろあって難しいんだ』という認識を持つことです。

とは書いていますが、以前よりも、問題的に深刻ではないと思います。

こちらは、あくまでも、Excel97以来のマクロの歴史の中での話でトラブルを紹介したけれども、それは、ある程度の経験を積めば、誰しも避けて通れないものではないかと思っています。

むろん、私は、日経BP21の高坂さんではなくて、芳坂さんでしたが、氏のサイトで、Findメソッドの問題に気が付きました。

もし、今回のように、=B1+TIMEVALUE("0:05") でドラッグ・コピーすれば、浮動小数点誤差の発生は免れえないし、それは、ドラッグ・コピーの方法の問題であって、マクロの問題ではないはすです。エクセルでは、よく陥るミスのひとつだと思います。

この問題を避けるために、固定小数点法として

以下の添付図で、このようにして、ドラッグして作ってみました。
=TEXT(A1,"hh:mm")+"0:05"　　(加算しているので、数値化はしています)

当然、ところどころ、普通のドラッグの数値とは、誤差として、False が出てきます。(図の赤字)

ところで、固定小数点法で作ったデータが重要ではなく、浮動小数点誤差のままで、マクロがヒットすれば問題ないわけです。

LookIn の部分をxlValues に変えてみました。
そうすれば、また問題なく検索します。

'//
Sub TestSearch()
Dim c As Range
Dim myTime As Variant
With ActiveSheet
myTime = .Range("A1").Text
Set c = .Range("B4:B1500").Find( _
　　What:=myTime, _
　　LookIn:=xlValues, _
　　LookAt:=xlWhole, _
　　SearchOrder:=xlByColumns)
　　If Not c Is Nothing Then
　　　　 c.Select
　　Else
　　　　 MsgBox "該当する時間は見つかりません。", vbExclamation
　　End If
End With
End Sub
'///

添付図で、赤色は、浮動小数点誤差が発生しています。

No.4 回答者： 30246kiku 回答日時： 2015/12/29 13:50

時間（時刻）の値は、0 ～ 1 の間で 24時間を表現しています

12 時なら 0.5　/　8 時なら 0.333333333333333000

添付図は、B1 に 8:00 / C1 には =B1 とし、書式を数値
B2 は、=B1+TIMEVALUE("0:05") / C2 には =B2
・・・
B25 まで、前の時間（時刻）に 5 分加算することを繰り返してます
B26 には、10:00 を直接入力してみると
C25 と C26 の数値が違うことがわかると思います

時間（時刻）部分を、どのように作成したかによって、
数値が微妙に異なる･･･

比較すると B25 <> B26 になるので、同じ 10:00 でも一致しないことに

時間（時刻）を比較する時には、＃１さんの様に、
各パート（日、時、分･･･）を求めて比較するか
Format(XXX,"yyyymmddhhnnss") の様に１つの文字列にしてから比較するか
した方が確実と思います。

No.3 回答者： WindFaller 回答日時： 2015/12/29 10:32

#2の回答者です。

#2の内容は、マクロの教科書などに載っている、基本中の基本なのですが、ご質問の解決とは別として、上から目線で恐縮なのですが、できたら、思い出した時でもよいので、目を通していただきたいと思っています。

これは、なぜかお仕着せでないと、うまくいかない現象があるのです。だから、成功か失敗を見越していました。

今回は、教わらないと自力では気が付きません。もちろん、私が勝手に作ったマッチポンプ式の題材ではあるけれども、ループ式のセルの検索は、よほどのことがない限りは避けたいところです。もちろん、アルゴリズムを使った解決方法はあっても、それは、VBAの範疇ではありません。

Findメソッドで日付・時間検索を使った場合に、すんなりと通過してしまう人と、解決できないままに、迷宮入りしてしまうタイプがあります。最初に、97時代に、日経BP21の高坂さんという人が書いた中で私は見つけました。どちらかというと、「バグ」に近い現象です。ただし、これは、Excel2007以上ではあまり聞かれなくなった問題です。

私自身、もう一度、3番目は、読み直さないと説明できるレベルではありません。

一つ目は、この問題

・省略可能なオプションを省略すると前回検索されたときの状況を引き継ぎます。
(初歩的なことですが、意外に忘れています。)
http://officetanaka.net/excel/vba/tips/tips131b. …

二つ目は、この問題
・検索時にセルの書式設定に左右されないようにするため、ｎ行目のLookInにxlFormulasを設定し、入力内容で検索する。

http://www.happy2-island.com/excelsmile/smile03/ …

3つ目は、この問題
(一言では説明できません。)

http://www.moug.net/tech/exvba/0050163.html

結びに、
》特効薬的な「こうすれば全てOK」という必殺技はありません。
》マクロで日付を検索するときに最も大事なことは『いろいろあって難しいんだ』という認識を持つことです。

と書かれてある次第です。(w)

そういう私は、いままでどうしていたのかというと、ワークシート関数(Match)を利用してきました。世の中には、何年もこんなことをあれこれと考えている人間がいるというぐらいでも構わないのですが、最初のTestSeachは、偶然のたものもです。Excel2010で行っています。

'//
Sub TestSearch()
Dim c As Range
Dim myTime As Variant　　'←ここをVariant型に変えたらヒットした
With ActiveSheet
myTime = .Range("A1").Value
Set c = .Range("B4:B1500").Find( _
　　What:=myTime, _
　　LookIn:=xlFormulas, _
　　LookAt:=xlWhole, _
　　SearchOrder:=xlByColumns)
　　If Not c Is Nothing Then
　　　　 c.Select
　　Else
　　　　 MsgBox "該当する時間は見つかりません。", vbExclamation
　　End If
End With
End Sub
'///

従来は、このような方法でやっていました。
.Range("B1:B1500")　B1からというのは、行数を取るためです。
Application.Match というのは古い書き方で、戻り値にエラー値を入れるためです。
ret は、Variant 型しかありません。

'//
Sub TestSearch2()
Dim ret As Variant
Dim myTime As Double　'←今度は、ここはVairant型では不可でした。
With ActiveSheet
myTime = .Range("A1").Value
　　ret = Application.Match(myTime, .Range("B1:B1500"), 0)
　　If Not IsError(ret) Then
　　　　 .Cells(ret, "B").Select
　　Else
　　　　 MsgBox "該当する時間は見つかりません。", vbExclamation
　　End If
End With
End Sub
'///

お暇でしたら、ちょっと試してみてください。

当然ですが、[日付値＋時間値]　の検索値で、非検索値も、[日付値＋時間値]であることが条件で、[日付値]だけなら、何ら問題はないものの、[時間値]が入るとややこしくなります。共に同じ条件の値でないと、検索はできないはずです。

No.2 回答者： WindFaller 回答日時： 2015/12/28 21:03

こんばんは。

ふつうは、以下のようなコードにはなるはずです。しかし、内部で違うとすると、「該当なし」になってしまいます。

If Rng.Value = Range("a1") Then
基本的には、これでもヒットするはずです。微妙なところで違っていたのかもしれません。

'//
Sub TestSearch()
Dim c As Range
Dim myTime As Date
With ActiveSheet
myTime = .Range("A1").Value
Set c = .Range("B4:B1500").Find( _
　　What:=myTime, _
　　LookIn:=xlFormulas, _
　　LookAt:=xlWhole, _
　　SearchOrder:=xlByColumns)
　　If Not c Is Nothing Then
　　　　 c.Select
　　Else
　　　　 MsgBox "該当なし。", vbExclamation
　　End If
End With
End Sub

'///

p.s. Activate と Select の違いは分かりますか？
Activate というのは、たくさん選んでいても、選んだまま、一つを選ぶということですが、Select は、選択したものは全部選びます、ということです。

この回答へのお礼

詳しい解説も付けていただきありがとうございます。本当に素人で、
良く理解できていないのが正直なところです。
一度、SETを使った物を作ったんですが、ごちゃ混ぜになってしまって
上手くいきませんでした。もっと勉強が必要ですね。
教えていただいたものを参考に色々試してみたいと思います。
ありがとうございました。

お礼日時：2015/12/29 00:55

No.1 回答者： siffon9 回答日時： 2015/12/28 16:21

If Rng.Value = Range("a1") Then　の部分を以下の様に変更したら如何でしょう？

If (Year(Rng.Value) = Year(Range("B1").Value)) _
And (Month(Rng.Value) = Month(Range("B1").Value)) _
And (Day(Rng.Value) = Day(Range("B1").Value)) _
And (Hour(Rng.Value) = Hour(Range("B1").Value)) _
And (Minute(Rng.Value) = Minute(Range("B1").Value)) Then

B列、C列には、年、月、日、時、分を表示していると仮定して、セルの値から各々の成分を抜き出して比較しています。もし年、月、日、などが表示されていない場合は、その成分の比較条件を削除してください。

この回答へのお礼

さっそく試してみました。上手くいきました。ありがとうございました。m(..)m

お礼日時：2015/12/28 17:25