VBA マクロ 条件が一致する別シートの時間を使って開始と終了の時間算出する方法をお教えください。V

VBA マクロ 条件が一致する別シートの時間を使って開始と終了の時間算出する方法をお教えください。VBA超Sheet1のD列の頭文字(2桁)と、sheet2のA列が一致するC列(準備時間)を使って
Sheet1の開始時間と終了時間を求めたいのです。
F5の開始時間は8:35からです。
今まではマクロの記録で作った下記で算出しておりましたが、（関数"=$F5+VLOOKUP(LEFT($D5,2),Sheet2!$A:$C,3)”を使って）10:00～10:30、12:00～13:00、15:00～15:30、17:00～17:30の時間を抜くことになりました。
もうどうすればいいのか全くのお手上げです(ﾉД｀;)
今はマクロを回した後、該当時間のセルを消して関数を入力するのを繰り返しております･･･。
これができるとすごくうれしいです。
よろしくお願いいたします。

Sub ボタン1_Click()
Range("F5").Select
ActiveCell.FormulaR1C1 = "8:35"
Range("G5").Select
Range("F6").Select
ActiveCell.FormulaR1C1 = "=R[-1]C7"
Range("F6").Select
Selection.AutoFill Destination:=Range("F6:F504")
Range("F6:F504").Select
Range("G5").Select
Selection.AutoFill Destination:="=RC6+VLOOKUP(LEFT(RC4,2),Sheet2!C1:C3,3)"
Selection.AutoFill Destination:=Range("G5:G504")
End Sub

Windows7でExcel2010を使っております。

補足です。
①Sheet2の準備時間は「分」です。
②終了時間が休憩時間に入り込んでしまった場合は休憩時間を足して終了時間を出す。
例えば開始時間が9:55で準備時間が２０分の場合、その終了時間は10:45が入るようにしたいです。『開始時間からの５分＋休憩時間３０分＋残りの準備時間１５分』
次の開始時間は10:45からです。
③マクロの中で時刻を計算して値を設定するで問題ありません。

No.1 ベストアンサー 回答者： ママチャリ 回答日時： 2016/09/18 21:21

補足③で良ければ、こんな感じです。

（厳密なテストはしていません。後は自己責任でお願いします）

Sub sample()
Dim i As Long
Dim StartTime As Date
Dim NextTime As Date
Dim S As Date
Dim N As Date
StartTime = "8:35"
For i = 5 To 504
NextTime = StartTime
On Error Resume Next
NextTime = NextTime + _
WorksheetFunction.VLookup(Left(Cells(i, "D"), 2), Sheets("Sheet2").Range("A:C"), 3, False)
On Error GoTo 0
S = TimeSerial(Hour(StartTime), Minute(StartTime), 0)
N = TimeSerial(Hour(NextTime), Minute(NextTime), 0)
If Not (N <= "10:00" Or S >= "10:30") Then NextTime = NextTime + "00:30"
If Not (N <= "12:00" Or S >= "13:00") Then NextTime = NextTime + "01:00"
If Not (N <= "15:00" Or S >= "15:30") Then NextTime = NextTime + "00:30"
If Not (N <= "17:00" Or S >= "17:30") Then NextTime = NextTime + "00:30"
Cells(i, "F").Value = StartTime
Cells(i, "G").Value = NextTime
StartTime = NextTime
Next i
End Sub

【機能制限】
標準時間が見つからない場合、0分と見做し、終了時間は開始時間と同値のままになります。
標準時間が2つ以上の休息時間に跨ることは考慮していません。

【気になる点】
例えば、前工程の終了時間が10:00で、次工程の標準時間が50分の場合、次工程の開始10:00、終了11:20（標準時間50分＋休息30分）となりますが、開始時間は10:00で良いのでしょうかね？
それから、深夜は休息なしですか？

この回答へのお礼

ありがとうございました！
希望通りの結果が得られました。
本当に助かりました。
またこちらで伺うこともあるかと思いますが、その際は是非よろしくお願いいたしますm(_ _)m

お礼日時：2016/09/19 23:19