
素人です。お願いします。
交代制勤務のシフト表(A列 シフト名、B列 始業時間、C列 終業時間)から、B列の時間を検索してその時間のシフト名を調べるマクロを作りたいのですが、上手くいきません。
A列のシフト名の検索は出来たのですが、時間の検索だとエラーがでてよくわかりません。
ちなみにシフト名の検索はこんな感じでうまくいきました。
userformを使いtextboxに検索したいシフトを入力し、該当があればそのセルを選択するようにしました。これを時間(B列)の検索に変えたいのですが、上手くいかないです。
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 回答 (6件)
- 最新から表示
- 回答順に表示
No.10
- 回答日時:
#4です
ご迷惑をおかけしました
私より先に回答つける方がいるかと思いましたが・・・
解決されていましたでしょうか?
解決に結びつくかわかりませんが、以下でデータを確認してみませんか
★部分を 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
ここで、値の個数が1個の場合、解決方法等まったくわかりません
ただ、複数個の場合、
> 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 の所が選択されましたが、
#4でのシートでは、エラー 2042 で確認できませんでした( 2007 )
こう記述すると、おかしいように思えるかもしれませんが、
#4のシートには 6:00 は無いので・・・
方法を提示された方の補足に記述されると、早い回答が得られるかも
回答者の多くは、
> そのスレが解決するということが第一
と考えられている人が多いのか・・・と期待はしています
余談)
#4での Format(XXX,"yyyymmddhhnnss") で変換してから・・・
これ、Access でも比較できない時があって、対処した方法だったので紹介してみました
ブログにチョット書いてましたが、URL 記述できないので・・・
Excel って賢いのですね
失礼しました
No.6
- 回答日時:
私は、
》#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
- 回答日時:
時間(時刻)の値は、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 でも一致しないことに
時間(時刻)を比較する時には、#1さんの様に、
各パート(日、時、分・・・)を求めて比較するか
Format(XXX,"yyyymmddhhnnss") の様に1つの文字列にしてから比較するか
した方が確実と思います。

No.3
- 回答日時:
#2の回答者です。
#2の内容は、マクロの教科書などに載っている、基本中の基本なのですが、ご質問の解決とは別として、上から目線で恐縮なのですが、できたら、思い出した時でもよいので、目を通していただきたいと思っています。
これは、なぜかお仕着せでないと、うまくいかない現象があるのです。だから、成功か失敗を見越していました。
今回は、教わらないと自力では気が付きません。もちろん、私が勝手に作ったマッチポンプ式の題材ではあるけれども、ループ式のセルの検索は、よほどのことがない限りは避けたいところです。もちろん、アルゴリズムを使った解決方法はあっても、それは、VBAの範疇ではありません。
Findメソッドで日付・時間検索を使った場合に、すんなりと通過してしまう人と、解決できないままに、迷宮入りしてしまうタイプがあります。最初に、97時代に、日経BP21の高坂さんという人が書いた中で私は見つけました。どちらかというと、「バグ」に近い現象です。ただし、これは、Excel2007以上ではあまり聞かれなくなった問題です。
私自身、もう一度、3番目は、読み直さないと説明できるレベルではありません。
一つ目は、この問題
・省略可能なオプションを省略すると前回検索されたときの状況を引き継ぎます。
(初歩的なことですが、意外に忘れています。)
http://officetanaka.net/excel/vba/tips/tips131b. …
二つ目は、この問題
・検索時にセルの書式設定に左右されないようにするため、n行目の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
- 回答日時:
こんばんは。
ふつうは、以下のようなコードにはなるはずです。しかし、内部で違うとすると、「該当なし」になってしまいます。
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を使った物を作ったんですが、ごちゃ混ぜになってしまって
上手くいきませんでした。もっと勉強が必要ですね。
教えていただいたものを参考に色々試してみたいと思います。
ありがとうございました。
No.1
- 回答日時:
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列には、年、月、日、時、分を表示していると仮定して、セルの値から各々の成分を抜き出して比較しています。もし年、月、日、などが表示されていない場合は、その成分の比較条件を削除してください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Visual Basic(VBA) シフト表のコマで「ブロック」されている前の時間の「出」を同一列の「休」と入れ替えたいがふぇきません。 2 2023/08/02 18:49
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) このVBAでExcelアプリケーションを作成は必要ですか? 3 2023/07/19 21:13
- Excel(エクセル) 指定文字列が該当するA列をアクティブセルにするには 3 2022/08/17 13:18
- Visual Basic(VBA) ExcelVBAのマクロについて。 9 2022/05/04 14:50
- Excel(エクセル) 製品番号での整列と、検索に関して 3 2023/06/28 19:20
- Excel(エクセル) 【Excel】指定のセル内容を基に別シートのセルを検索して選択する【VBA】 1 2022/06/16 16:16
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・「これはヤバかったな」という遅刻エピソード
- ・初めて自分の家と他人の家が違う、と意識した時
- ・いちばん失敗した人決定戦
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ExcelVBAを使って、値...
-
Excelで指定した日付から過去の...
-
エクセルvbaで、別シートの最下...
-
【Excel VBA】指定行以降をクリ...
-
【Excel】指定したセルの名前で...
-
VBA実行後に元のセルに戻りたい
-
特定のセルが空白だったら、そ...
-
Excel VBA マクロ ある列の最終...
-
vba 隣のセルに値がある行だけ...
-
VBA コピーして次の値まで貼り...
-
VBA初心者です。結合セルを保持...
-
VBAでセルをクリックする回...
-
VBSでエクセル内の行数を取得す...
-
i=cells(Rows.Count, 1)とi=cel...
-
”戻り値”が変化したときに、マ...
-
特定の文字を条件に行挿入とそ...
-
ExcelのVBAで数字と文字列をマ...
-
3桁または4桁の数値を時刻に...
-
Excelのプルダウンで2列分の情...
-
クリックされたセルの位置を取...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ExcelVBAを使って、値...
-
Excelで指定した日付から過去の...
-
i=cells(Rows.Count, 1)とi=cel...
-
【Excel VBA】指定行以降をクリ...
-
VBA実行後に元のセルに戻りたい
-
特定のセルが空白だったら、そ...
-
【Excel】指定したセルの名前で...
-
”戻り値”が変化したときに、マ...
-
【EXCEL VBA】Range("A:A").Fi...
-
エクセルvbaで、別シートの最下...
-
連続する複数のセル値がすべて0...
-
セル色なしの行一括削除
-
Excel VBA マクロ ある列の最終...
-
VBAでセルをクリックする回...
-
特定の文字を条件に行挿入とそ...
-
Excel vbaで特定の文字以外が入...
-
Excelのプルダウンで2列分の情...
-
VBからEXCELのセルの値を取得す...
-
EXCELで変数をペーストしたい
-
vba 隣のセルに値がある行だけ...
おすすめ情報
色々な角度から回答をいただきありがとうございました。いろいろ試してみましたが、なかなかうまく検索してくれません。
a/コード b/実動 c/始業 d/終業
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
続く
表は実動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
各時間とも先頭を選んだり、途中を選んだりとまちまちです。
書式?形式?が違うのでしょうか?本当に素人でスミマセン。