文字列になっている時間をVBAで処理できません。

Question

B列に”(PM有休)”という値があるのに、E列が16：30の4.5時間前より遅い時、E列を黄色に塗りつぶししたいです。

なお、B列の予定は人や季節によって30分単位で異なり、8：00や夕方に出勤する人もいます。その場合も、B列のスケジュールに入っている退勤予定時間の4.5時間前よりE列の時間が遅い場合はE列を黄色に塗りつぶししたいです。

B列もE列もその他の全てのセル値は数値ではなく文字列になっています。
B列の値は例えば”7:30~16:30 (PM有休)”、E列の値は”位置(携帯) 11:30（島根県○○市”のように入っています。
これは、システムから出力した際に自動で文字列になります。
使用するシートはActiveSheetのみで、検索は17行目からですが、最終行は月によって異なります。
現状文字列になっている時間を処理する事が出来ません。
どのようにコードを組めばいいのでしょうか。ご教示ください。

fujillin · Accepted Answer

No2です。

反応がないみたいですけれど・・・
不明点がいろいろあるので、勝手に想定しました。

・B列に"(PM有休)"を含む行について、2番目に記載の時刻より4.5時間前よりも、E列に含まれる時刻が遅ければ、E列のセルの背景色を黄色にする。
・上記以外（時刻表記がない等も含む）の場合は、E列の背景色をなしにする。
と解釈しました。

※ 特に「(PM有休)」の文字列は、半角全角文字などいろいろ考えられますが、実際は固定の文字列だと思いますので、実態に合わせてください。

以下、ご参考までに。

Sub Q13801125()
Dim Reg, m
Dim d As Date, TL As Date
Dim i As Long, col As Long

TL = TimeValue("4:30")
Set Reg = CreateObject("VBScript.RegExp")
Reg.Pattern = "\d{1,2}:\d{1,2}"
Reg.Global = True

For i = 17 To Cells(Rows.Count, 2).End(xlUp).Row
　col = xlNone
　txt = Cells(i, 2).Text
　If InStr(txt, "(PM有休)") Then
　　Set m = Reg.Execute(txt)
　　If m.Count > 1 Then
　　　d = TimeValue(m(1).Value)
　　　Set m = Reg.Execute(Cells(i, 5).Text)
　　　If m.Count > 0 Then
　　　　If d - TimeValue(m(0).Value) < TL Then col = vbYellow
　　　End If
　　End If
　End If
　Cells(i, 5).Interior.Color = col
Next i
End Sub

zircon3 · Answer

No.1です。

> この表はシステムから吐き出された状態であり、

> 「様式を見直す」ことは現実的ではないと考えます。

・・・という現状の事情を優先してということであれば単純な数値（時間）を比較しての処理はできないので、時間＋付加情報を記した文字列の文字解析を行って時間情報を抽出して判断するロジックを作成するしかありません。
Excelファイルの各列の内容が他のシステムの出力によるものであれば様式（B列の1文字目から4文字は作業開始予定時間で・・・といった書式）は決まっているでしょうから十分可能です。
ただ、0時から9時は時間が1文字で、以降は2文字といったことがあるとロジックが少しだけ面倒ですね。「2文字目が:なら・・・」といった判別が必要になりますので。
D列、E列についても同様です。各列の書式を把握してそれに則した文字列処理のロジックを作成されればよいかです。

なお、処理を出来るだけ簡単にするにはNo.1で当方が示したような時間を数値に変換したワーク用のシートをマクロ内で追加し、このシート内のデータを使ってチェックをし、検出した行と同じ行番号の元シート内の行のE列の背景色を黄色にすればOKでしょう。
作業を終えたらマクロの終わりにワークシートを削除する処理を設ければ完成と。

ただ、本質的な解決はExcelのデータを出力している上流システムを下位システム（この場合Excelのマクロ）が処理しやすい物となるよう改修することです。
30年ほどその方面の仕事をしてた元システム開発者としてはそう考えます。

参考まで。

googoo900 · Answer

自動で書き出されたA～F列はそのまま残して、型変換したものを未使用列（例えばH列以降）に書き込むようにするのはいかがでしょうか？
あわせてB列やE列などのように複数項目が書き込まれている列は、1項目/1列となるように記載列を分けるようにします。

・時間を文字列から時刻へ変換するのは、マクロだけで行うよりもワークシートを活用して行うほうが容易だと思います。
・画像にある『システムから吐き出された状態』の表は複数項目が1つのセルに書き込まれており、計算するのに適していません。マクロで処理できるのですが、こちらもワークシート上で1項目/1セルになるよう前処理しておくほうがマクロ作成が容易になります。

H列以降は計算のための暫定のものなので、計算後は残しておく必要はありません。

tatsumaru77 · Answer

＞B列に”(PM有休)”という値があるのに、E列が16：30の4.5時間前より遅い時、E列を黄色に塗りつぶししたいです。

この意味の確認です。
要件は、B列に(PM有休)の文字がある場合、E列が12:00（16:30の4.5時間前は12:00）以降の時間なら、E列を黄色に塗りつぶしたいということでしょうか。
従って、E26の値は11:30なので、塗りつぶさない。
塗りつぶすのは、E26の値が12:00以降の場合である。
ということであってますか？
又、この場合の16:30は、固定の値ではなく、B列の退勤時間である16:30を示していますか？
もし、B列に8:00～17:00と設定されていた場合は、E列が12:30以降なら塗りつぶすということでしょうか。

従って、要件をまとめると、E列を黄色で塗りつぶす条件は、
①B列に (PM有休)の文字があること。但し、PMは半角、両端の括弧()も半角であること。
②E列の時間が、B列の退勤予定時間の4.5時間前以降の時間であること。

fujillin · Answer

こんにちは

＞文字列になっている時間をVBAで処理できません。
文字列のままで計算をしようとすると、それなりに面倒と思います。
時間計算をなさりたいのなら、Date型の値に換算してから計算するようにしておくことで、計算は簡単になるでしょう。

VBAの TimeValue 関数を利用することで、「時刻表示形式の文字列」をDate型に変換することができます。
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/timevalue-function

zircon3 · Answer

将来性も考えExcelシートの様式を見直すのが最も適切な方法だと考えます。
例えば列構成を以下のようにするとか。

日付
種別
出勤予定時間　　※属性は“時間”
退勤予定時間　　※属性は“時間
休暇
出勤時間　　　　※属性は“時間
退勤時間　　　　※属性は“時間
休息
付加情報　　　　※出勤時間や退勤時間などの入力がどの地域からされたといった付加情報

出勤、退勤の予定時間と実際の時間との間に休暇を入れたのは人の入力誤りを防止することを意図してです。

参考まで。

文字列になっている時間をVBAで処理できません。

No2です。

No.1です。

自動で書き出されたA～F列はそのまま残して、型変換したものを未使用列（例えばH列以降）に書き込むようにするのはいかがでしょうか？

＞B列に”(PM有休)”という値があるのに、E列が16：30の4.5時間前より遅い時、E列を黄色に塗りつぶししたいです。

こんにちは

将来性も考えExcelシートの様式を見直すのが最も適切な方法だと考えます。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング