[[20190608180910]]　『土日祝日を除いた5日前を同じ行の別の列に表示し＝x（dawny）　ページの最後に飛ぶ

『土日祝日を除いた5日前を同じ行の別の列に表示し、背景色をつける』（dawny）

初めまして。
力及ばず書き込ませて頂きます。

Sheet1には「管理表」
Sheet2には「holiday」という名前がついています。

「管理表」のシートに上から6行目まで商品名や受付日などの項目があり、7行目からは１年分データが入り続けます。
6行目の項目名に以下があります。

U列（納品日）
W列（発送日）

【やりたいこと1】
U列に納品日を入れると、W列の発送日には土日祝日を除いた5営業日前の日付が入り、背景色がつくようにしたいのです。

【やりたいこと2】
U列の値を消すとWの値も消えるようにしたい

【課題1】
U列に日付を入れて「TAB」キー押下で実現できるのですが、
「Enter」キーですと、実行できなくなってしまいます。
不特定多数の人がこのファイルを操作するので、
どのような操作でもU列に値が入ったら同じ行のW列に発送日が入るようにしたいです。

【課題2】
U列を1セルのみ指定して削除する（Delete）と同じ行のW列の値も消えるのですが、U列を複数セル選んだ状態でDeleteすると、W列の値が残ってしまいます。

以下が実際のコードです。

'-----------------------------------------------------
標準　Module1
'-----------------------------------------------------
Function getWorkDay(day As Date, diff As Long) As Date

  Dim holidaySht As Worksheet
  Set holidaySht = ThisWorkbook.Worksheets("holiday")

  Dim lastRow As Long
  lastRow = getMaxRow(holidaySht, 1)

      getWorkDay = Application.WorksheetFunction.WorkDay(day, diff, holidaySht.Range("A2:A" & lastRow))

  Set holidaySht = Nothing

End Function

Function getMaxRow(sht As Worksheet, targetCol As Long) As Long

  getMaxRow = sht.Cells(sht.Rows.Count, targetCol).End(xlUp).Row    'holidayシートの最終行を取得

End Function

Sub test_getWorkDay()

Dim 納品日U As Date
Dim 出荷日W As Date

納品日U = Range("U" & ActiveCell.Row)
出荷日W = Cells(Selection.Row, 23)

Range("W" & ActiveCell.Row) = getWorkDay(納品日U, -5)
Range("W" & ActiveCell.Row).Select

With Selection.Interior

    .Color = RGB(255, 204, 0)
End With
With Selection.Font
    .ThemeColor = xlThemeColorDark1
End With

End Sub

'-----------------------------------------------------
sheet1（管理表）
'-----------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If Application.Intersect(Range("U7:U10000"), Target) Is Nothing Then Exit Sub
If .Count > 1 Then Exit Sub
If IsEmpty(.Value) Then
.Offset(, 2).Clear
Else

Call test_getWorkDay

End If
End With
End Sub

列の指定や削除の指定が間違っているのでしょうか。
お力添え頂けますと幸いです。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

回答ではありません。
条件付き書式はご存知ですか？

（マナ） 2019/06/08(土) 19:03

あと、こんな関数もあります。
https://www.becoolusers.com/excel/workday-intl.html

（マナ） 2019/06/08(土) 19:05

マナさん、ありがとうございます。
実は土日祝日をカウントしないだけではなく、不規則に発生する発送禁止日という日があり、holidayシートに随時追記していくような形になっています。

関数ですと、excelに詳しくない管理対象外の作業者が勝手に行追加などを行った際、
条件付き書式が反映されなくなり、作業漏れが発生する事を懸念しております。

（また今後、W列に入った日付をダブルクリックすると本日の日付を記入し、定型文のメールを作成するように動作させるつもりです。）
（dawny） 2019/06/08(土) 19:22

 複数セルとなると、休日リストデータの取得は1回だけにしたいなぁ。

 そうすると、getWorkDay　の存在意義がなくなりますかねぇ。。

 test_getWorkDay のネーミングがピンと来ないんですが、下請けに出さないとならないんでしょうか？
 Changeイベント内で処理したくなっちゃいます。

 すると、再利用できそうな標準モジュールのプロシージャとしては
 getMaxRow()だけしか残りませんが、それでいいですか？

 '-----------------------------------------------------
 'Sheet1 (管理表)
 '-----------------------------------------------------
 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim WSF As WorksheetFunction
     Dim aTgt As Range
     Dim holidaySht As Worksheet
     Dim HolidayList As Range
     Dim lastRow As Long

     If Application.Intersect(Range("U7:U10000"), Target) Is Nothing Then Exit Sub

     Set WSF = WorksheetFunction
     Set holidaySht = ThisWorkbook.Worksheets("holiday")

     lastRow = getMaxRow(holidaySht, 1)
     Set HolidayList = holidaySht.Range("A2:A" & lastRow) 'ここで１回取得すればいい

     For Each aTgt In Target
         If IsEmpty(aTgt.Value) Then
             aTgt.Offset(, 2).Clear
         Else
             With aTgt.Offset(, 2)
                 .Value = WSF.WorkDay(aTgt.Value, -5, HolidayList)
                 .Interior.Color = RGB(255, 204, 0)
                 .Font.ThemeColor = xlThemeColorDark1
             End With
         End If
     Next aTgt
 End Sub

 '-----------------------------------------------------
 '標準 Module1
 '----------------------------------------------------- 
 Function getMaxRow(sht As Worksheet, targetCol As Long) As Long
     getMaxRow = sht.Cells(sht.Rows.Count, targetCol).End(xlUp).Row  'holidayシートの最終行を取得
 End Function

（半平太） 2019/06/09(日) 00:28

半平太さん

情報不足で申し訳ないです。

日付が変わっても５営業日前の日付が発送日に記載されるようにしたいのです。
納品日はコロコロ変わる可能性がありますので、U列は何度も書き変わります。
U列を一度消し、もう一度日付を入力すると、W列には「43619」のような書式になってしまいます。

また、他の列にも1次納品（AA）、1次発送（AC）、2次納品（AE）、２次発送（AG）というのがあり、
同じような動作を入れるような形になります。

「test_getWorkDay」のネーミングには深い意味はありません。
このファイルを外部に提出することもありませんので、変更も可能です。
（いろんな人がコードをいじって何年もつぎはぎで使っているので、このようになっていました）

（dawny） 2019/06/09(日) 14:22

 ＞W列には「43619」のような書式になってしまいます。

 ＞             aTgt.Offset(, 2).Clear
 　　　　　　　　　　　↓へ変更
             aTgt.Offset(, 2).ClearContents
             aTgt.Offset(, 2).Interior.Color = xlNone

 ＞また、他の列にも1次納品（AA）、1次発送（AC）、2次納品（AE）、２次発送（AG）というのがあり、 
 ＞同じような動作を入れるような形になります。

 ・・と言われましても、何が問題なんですか？
 (1)　同じシートの(AA）列の話ですか？　それとも別のシートの話？
 (2)　反映すべき右隣のセルの位置が2つの場合だけじゃないとか言う話ですか？
 (3)　計算すべき営業日数の違いの話ですか？

 そう言う事が分からないとこちらでは対処しようがありません。
 もしくは、
 上のコードで、Enterでも、複数のセルのケースでも対応できるようになっていますので、
 私の案を参考に、ご自身でアレンジしてください。

（半平太） 2019/06/09(日) 15:29

半平太さん

ありがとうございました、大変助かりました。
折角教えて頂いたのに適切に説明出来ず、
困惑させてしまったようで申し訳ないです。

勉強不足を痛感致しましたので、自分なりに調べてみようと思います。
（dawny） 2019/06/09(日) 15:41

コメント返信：

[ 一覧(最新更新順) ]

[[20190608180910]] 『土日祝日を除いた5日前を同じ行の別の列に表示し＝x（dawny） ページの最後に飛ぶ

[[20190608180910]]　『土日祝日を除いた5日前を同じ行の別の列に表示し＝x（dawny）　ページの最後に飛ぶ