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行目からですが、最終行は月によって異なります。
現状文字列になっている時間を処理する事が出来ません。
どのようにコードを組めばいいのでしょうか。ご教示ください。
No.6ベストアンサー
- 回答日時:
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
fujillin様お礼が遅くなり申し訳ありませんでした。
TimeValue 関数を使ってみましたが時間の前後の値の処理がうまくかず滞っていたところ、こちらで回答いただいたコードではやりたい事が実現できました。私には難しいコードですので、とにかくまずコードの解読を頑張ります。前回の質問に引き続きありがとうございました。
No.5
- 回答日時:
No.1です。
> この表はシステムから吐き出された状態であり、
> 「様式を見直す」ことは現実的ではないと考えます。
・・・という現状の事情を優先してということであれば単純な数値(時間)を比較しての処理はできないので、時間+付加情報を記した文字列の文字解析を行って時間情報を抽出して判断するロジックを作成するしかありません。
Excelファイルの各列の内容が他のシステムの出力によるものであれば様式(B列の1文字目から4文字は作業開始予定時間で・・・といった書式)は決まっているでしょうから十分可能です。
ただ、0時から9時は時間が1文字で、以降は2文字といったことがあるとロジックが少しだけ面倒ですね。「2文字目が:なら・・・」といった判別が必要になりますので。
D列、E列についても同様です。各列の書式を把握してそれに則した文字列処理のロジックを作成されればよいかです。
なお、処理を出来るだけ簡単にするにはNo.1で当方が示したような時間を数値に変換したワーク用のシートをマクロ内で追加し、このシート内のデータを使ってチェックをし、検出した行と同じ行番号の元シート内の行のE列の背景色を黄色にすればOKでしょう。
作業を終えたらマクロの終わりにワークシートを削除する処理を設ければ完成と。
ただ、本質的な解決はExcelのデータを出力している上流システムを下位システム(この場合Excelのマクロ)が処理しやすい物となるよう改修することです。
30年ほどその方面の仕事をしてた元システム開発者としてはそう考えます。
参考まで。
2度もご教示いただきありがとうございます。
ご教示いただいた内容を元にコードの作成を頑張っては見たのですがうまくいかず、こちらに戻ってみたところコードをご提示いただいた方のコードで思った通りの動きができましたので、そちらの方にベストアンサーを差し上げたいと思います。申し訳ありません。
No.4
- 回答日時:
自動で書き出されたA~F列はそのまま残して、型変換したものを未使用列(例えばH列以降)に書き込むようにするのはいかがでしょうか?
あわせてB列やE列などのように複数項目が書き込まれている列は、1項目/1列となるように記載列を分けるようにします。
・時間を文字列から時刻へ変換するのは、マクロだけで行うよりもワークシートを活用して行うほうが容易だと思います。
・画像にある『システムから吐き出された状態』の表は複数項目が1つのセルに書き込まれており、計算するのに適していません。マクロで処理できるのですが、こちらもワークシート上で1項目/1セルになるよう前処理しておくほうがマクロ作成が容易になります。
H列以降は計算のための暫定のものなので、計算後は残しておく必要はありません。
googoo900様
ご回答ありがとうございます。
ご提示いただいた内容の場合、削除に時間がかかる為できれば避けたい方法と思います。申し訳ありません。
No.3
- 回答日時:
>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時間前以降の時間であること。
tatsumaru77様のご認識の通りです。
①B列に (PM有休)の文字があること。但し、PMは半角、両端の括弧()も半角であること。
②E列の時間が、B列の退勤予定時間の4.5時間前以降の時間であること。
となります。
No.2
- 回答日時:
こんにちは
>文字列になっている時間をVBAで処理できません。
文字列のままで計算をしようとすると、それなりに面倒と思います。
時間計算をなさりたいのなら、Date型の値に換算してから計算するようにしておくことで、計算は簡単になるでしょう。
VBAの TimeValue 関数を利用することで、「時刻表示形式の文字列」をDate型に変換することができます。
https://learn.microsoft.com/ja-jp/office/vba/lan …
時間の前後の値の処理がうまくいきませんでしたが、この後ご提示いただいたコードで実現できました。過不足の多い質問を解読していただきありがとうございました。
No.1
- 回答日時:
将来性も考えExcelシートの様式を見直すのが最も適切な方法だと考えます。
例えば列構成を以下のようにするとか。
日付
種別
出勤予定時間 ※属性は“時間”
退勤予定時間 ※属性は“時間
休暇
出勤時間 ※属性は“時間
退勤時間 ※属性は“時間
休息
付加情報 ※出勤時間や退勤時間などの入力がどの地域からされたといった付加情報
出勤、退勤の予定時間と実際の時間との間に休暇を入れたのは人の入力誤りを防止することを意図してです。
参考まで。
ご回答ありがとうございます。
この表はシステムから吐き出された状態であり、このExcelで間違った運用箇所に目印をつけ、システムを正しい処理に修正してもらう為のExcelです。できるだけ元の状態と見た目を変えたくありませんしマクロの実行時間もできるだけ短くしたいと考えます。
説明が足りず申し訳ありませんがご指摘いただいた「様式を見直す」ことは現実的ではないと考えます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) EXCEL VBA 単語置き換え について質問です ブック名 ぶぶぶ シート名 ししし セル V3〜 3 2023/03/08 01:41
- Visual Basic(VBA) VBAを使用した時間管理 4 2024/03/25 12:45
- Visual Basic(VBA) Excel VBA 文字列のセルを反映させたいです 2 2024/02/24 00:06
- Java Java 南京錠 2 2023/02/04 11:46
- Visual Basic(VBA) 【再投稿】VBAで動作しなくて困っています 2 2022/10/11 11:05
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) エクセル関数に詳しい方、教えてください(テキスト関数と条件付書式) 1 2024/04/11 02:44
- Java javaについて 1 2023/12/06 20:08
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Excel(エクセル) エクセル 応用した置換方法 3 2023/10/07 18:24
このQ&Aを見た人はこんなQ&Aも見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
Excelでの時間帯の入力
Excel(エクセル)
-
自動入力で今月はMONTH(TODAY())で良いのですが、翌月はどのようにすれば良いでしょうか。
Excel(エクセル)
-
Excelについて教えてください
Excel(エクセル)
-
-
4
エクセルの「入力規則」のプルダウンメニュの▼をセルの左側に出したい
Excel(エクセル)
-
5
行数が不規則な一週間ごとの合計値の計算をVBAで実行したいです。
Excel(エクセル)
-
6
時間によってファイル名が変わるエクセルをほかのエクセルでデータを参照する方法
Excel(エクセル)
-
7
CSVファイルについて質問です。
Excel(エクセル)
-
8
エクセル②
Excel(エクセル)
-
9
エクセルをエクセレントに究める
Excel(エクセル)
-
10
データチェックを行うエクセルマクロをおしえてほしい
Excel(エクセル)
-
11
エクセルのクイックアクセスツールバーには何を登録したら良いですか?罫線を引く「格子・枠なし・外枠」と
Excel(エクセル)
-
12
スプレッドシートの関数について教えてください
Excel(エクセル)
-
13
再質問です。マクロの修正箇所を教えてください。
Excel(エクセル)
-
14
エクセル関数に詳しい方教えてください
Excel(エクセル)
-
15
Excel初心者です。 詳しい方、効率の良いやり方を教えてください。 職場で、抽出したデータの表を見
Excel(エクセル)
-
16
Excelにてある膨大なデータを管理しています。 そこで、特定の市町村にのみ色を付けたいです。 Ex
Excel(エクセル)
-
17
エクセルで指定した日付、店舗の数値を自動で反映させる。
Excel(エクセル)
-
18
excel で二つのどちらかを選ぶ
Excel(エクセル)
-
19
【マクロ】フォルダからエクセルファイルの名前を取出すためには
Excel(エクセル)
-
20
MOS365 Excel Expert / Excel RANDARRAY 関数について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
PowerPointで表の1つの列だけ...
-
エクセルで二つの数字の小さい...
-
エクセル 同じ値を探して隣の...
-
エクセルで最初のスペースまで...
-
エクセル(勝手に太字になる)
-
VBAで文字列を数値に変換したい
-
エクセルの表から正の数、負の...
-
Excelで半角の文字を含むセルを...
-
エクセルで文字が混じった数字...
-
エクセル 文字数 多い順 並...
-
A列がない・・・A列が非表示に...
-
2つのエクセルのデータを同じよ...
-
文字列に数字を含むセルを調べたい
-
エクセルの並び変えで、空白セ...
-
エクセル 時間帯の重複の有無
-
オートフィルターをかけ、#N/A...
-
EXCELで 一桁の数値を二桁に
-
【エクセル】指定した文字以外...
-
条件付き書式の適用先が変更さ...
-
年が明けたら1年プラスされる...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
2つのエクセルのデータを同じよ...
-
エクセル 文字数 多い順 並...
-
VBAで文字列を数値に変換したい
-
エクセル(勝手に太字になる)
-
エクセルで文字が混じった数字...
-
「B列が日曜の場合」C列に/...
-
エクセルの表から正の数、負の...
-
Excelで半角の文字を含むセルを...
-
エクセル 同じ値を探して隣の...
-
Excel、市から登録している住所...
-
エクセルの項目軸を左寄せにしたい
-
EXCELで 一桁の数値を二桁に
-
VBA 連続行データを5行ずつ隣の...
-
エクセル 時間帯の重複の有無
-
エクセルで、列の空欄に隣の列...
-
オートフィルターをかけ、#N/A...
-
A列がない・・・A列が非表示に...
おすすめ情報