[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シフト作成、1 コードの添削宜しくお願い致します。』（なのれい）
いつもお世話になっております。
皆様ご存じだと思うんですが、シフト作成を行っております。
自分のコードが甘いので添削して頂けないでしょうか？
宜しくお願い致します。
条件
・連続勤務6日以下になる事
・休み（公）の前日は早番（Ａ）、次の日は遅番（Ｂ）
・月の公休は6日間
・出勤のサイクルとしてＢＡ公の順になる
例 ＢＢＢＡＡ公ＢＢＡ公ＢＡＡ
ＡＢのパターンにはならないってことです。
・上記条件に当てはまらない従業員も数名います。
・希望休を手入力するタイミングが欲しい
前回との変更点
・上記条件の場合だと、最小勤務日数が2日間になるのでこれを3日間にしたい。
Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ Ｉ Ｊ Ｋ 6 2019 10 月 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 7 火 水 木 金 土 日 月 火 水 木 金 土 日 月 火 水 木 金 土 日 月 火 水 木 金 土 日 月 火 水 木 8 名前 入替 9 佐藤 B A 公 B B Ａ 10 工藤 B B Ａ Ａ A 公 11 伊藤 A 公 B B B Ａ 12 加藤 公 B Ａ Ａ 公 B
1日から25日は省略しています。
下方向に氏名、右方向に日にち、は決まりです。また日にちの下の行は曜日、その下の行は（入替）を入力する行が必要になります。
添削して頂きたいコード
Sub Macro4()
Dim 氏名最終行 As Long, 末日 As Long, i As Long
With Worksheets("作成シート")
氏名最終行 = Range("C6").End(xlDown).Row
末日 = Cells(4, Columns.Count).End(xlToLeft).Column
Range("D7:L" & 氏名最終行).Clear
Range(Cells(7, 末日), Cells(氏名最終行, 末日)).Copy Range("G7")
For i = 7 To 氏名最終行
最終公休日 = Range(i & ":" & i).Find(What:="公", SearchDirection:=xlPrevious).Column
月末連勤数 = 末日 - 最終公休日
Cells(i, 6).Value = 月末連勤数
If Cells(i, 7) = "B" Then
Cells(i, 8).Value = "出"
End If
If Cells(i, 6) <= "2" Then
Cells(i, 8).Value = "出"
If Cells(i, 6) <= "1" Then
Cells(i, 9).Value = "出"
If Cells(i, 6) <= "0" Then
Cells(i, 10).Value = "出"
End If
End If
End If
Next i
If Range("D4").Value < 12 Then
Range("D4") = Range("D4") + 1
Else
Range("D4") = 1
Range("C4") = Range("C4") + 1
End If
Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
問題点
末日 = Cells(4, Columns.Count).End(xlToLeft).Column
最後の列を取得したいんですが、30日の月も31日に数式が入っている状態ですので31の列を取得してしまいます。ここを30の列を取得したいです。
31のセルには=IFERROR(IF(EOMONTH(AK4,0)=EOMONTH(AK4+1,0),AK4+1,""),"")が入っております。
そのほかは問題なく動いておりますが、もっと簡潔に出来るのなら簡潔にしたいです。
添削宜しくお願い致します。
＜ 使用 Excel：unknown、使用 OS：unknown ＞
今月のシフトが入ってる状態から来月のシフトを作成していきます。
まず上記のコードを行いたいという事です。
上記のコードが終わったら、希望休を手入力で入れていく予定です。
（なのれい） 2019/09/29(日) 21:35
（γ） 2019/09/30(月) 00:11
■１
Withステートメントを使っているけど、.が無いから意味がなくなっている。
■2
↓は
If Cells(i, 6) <= "2" Then
Cells(i, 8).Value = "出"
If Cells(i, 6) <= "1" Then
Cells(i, 9).Value = "出"
If Cells(i, 6) <= "0" Then
Cells(i, 10).Value = "出"
End If
End If
End If
こういう書き方もありでは？
（なぜか文字列を比較していたので直しました）
If Cells(i, 6) <= 2 Then
Cells(i, 8).Value = "出"
ElseIf Cells(i, 6) <= 1 Then
Cells(i, 9).Value = "出"
ElseIf Cells(i, 6) <= 0 Then
Cells(i, 10).Value = "出"
End If
で、整理すると、「1」「0」「-1以下（あるのかわからないけど）」も、「f Cells(i, 6) <= 2 Then」が真になるから、 ElseIfの部分の意味がなくなってます
■3-1
質問の方は月末日の列を取得したいのですよね？
VBAで月末日を取得する方法の１例は、前トピックで示してますよ。
↓より抜粋 [[20190914110320]] 『CALLでプログラムを呼ぶと上手く処理されない』（なのれい） （もこな２ ） 2019/09/27(金) 19:08
'▼シリアル値から月末日を数値で算出
月末日 = Day(DateSerial(Year(Range("H5")), Month(Range("H5")) + 1, 0))
■3-2
したがって、1日が入ってる列（例えばH列）から.offset(,月末日-1）すれば、月末の列ですね。
.Cells(i, "H").Offset(, 月末日-1)
前トピックでは、セルの集まりを掴みたかったので↓みたいに示しましたが。
'▼1日〜月末までループ処理 For Each MyRNG In .Cells(i, "H").Resize(, 月末日)
（もこな２） 2019/09/30(月) 00:38
あと最終連勤数周りがまわりくどいように見えます。
わざわざ最終連勤を出したいというのでもなければ
月初3勤を出すのに末日をシフトをG列に持って来たりF列に最終連勤数を出したりしなくても
if 末日が「公」 then 8,9,10列目は「出」
elseif 末日の一つ左が「公」 then 8.9列目は「出」
elseif 末日の二つ左が「公」か末日が「B」 then 8列目は「出」
という式で十分なように見えます。
（黄色い循環参照） 2019/09/30(月) 01:35
Sub Macro4()
Dim 氏名最終行 As Long, 月末日 As Long, i As Long
With Worksheets("作成シート")
氏名最終行 = Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(Year(Date), Month(Date), 0), "d") + 7
Range("D7:L" & 氏名最終行).Clear
For i = 7 To 氏名最終行
Cells(i, 7).Value = 月末日 - Range(i & ":" & i).Find(What:="公", SearchDirection:=xlPrevious).Column
If Cells(i, 月末日) = "B" Then
Cells(i, 8).Value = "出"
End If
If Cells(i, 7) <= 2 Then
Cells(i, 8).Value = "出"
If Cells(i, 7) <= 1 Then
Cells(i, 9).Value = "出"
If Cells(i, 7) <= 0 Then
Cells(i, 10).Value = "出"
End If
End If
End If
Next i
If Range("D4").Value < 12 Then
Range("D4") = Range("D4") + 1
Else
Range("D4") = 1
Range("C4") = Range("C4") + 1
End If
Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
また説明不足でした。（出）は出勤確定で、後々（Ａ）（Ｂ）のどちらかを入れるつもりです。
コード修正致しました。
変更した点
・今月末の最終列を取得
・今月末のシフトが（Ｂ）なら1日の列を（出）にする
・最終日のシフトを省いたので、Cells(i, 7)に連勤の日数を持ってきました。
■1 今回のコードの場合、Withステートメントは必要ないですか？
■2は上手く行かなったので現状のままにしております。
■3月末の日は上記のコードで解決出来てると思っております。どうでしょうか？
・
（なのれい） 2019/09/30(月) 02:57
ただ、標準モジュールに記述しているなら、↓のようなことになってます。
With Worksheets("あいう")
Range("A1").Value = ""
'↓「.」をつけ忘れるとこうなる
ActiveSheet.Range("A1").Value = ""
.Range("A1").Value = ""
'↓ちゃんと「.」をつけるとこうなる
Worksheets("あいう").Range("A1").Value = ""
End With
前に提示したような気もしますが、参考です。
http://officetanaka.net/excel/vba/beginner/16.htm
＞■2は上手く行かなったので現状のままにしております。
なにがどう上手く行かなったかわかりませんが、↓をステップ実行してなんでC1セルではなく、B1セルに書き込まれるのか考えてみてください。
Sub 実験用8()
Range("A1").Value = 1
If Range("A1").Value <= 2 Then
Cells(1, "B").Value = "出"
ElseIf Range("A1").Value <= 1 Then
Cells(1, "C").Value = "出"
ElseIf Range("A1").Value <= 0 Then
Cells(1, "D").Value = "出"
End If
End Sub
＞■3月末の日は上記のコードで解決出来てると思っております。どうでしょうか？
実行日 実行日 0
↓ ↓ ↓
月末日 = Format(DateSerial(Year(Date), Month(Date), 0), "d")
なので、2019/9/30に実行したら、 2019/9/0 → 2019/8/31 → 31 になりますね。
（コード上では実際には何故か7足してますけど）
マクロを動かした日からみて先月末日を知りたいなら正解？ですかね。
まぁ、ご自身で試しているのでしょうから問題なければいいんじゃないですか？
私なら、前トピックのように、カレンダーから対象の年月日を取得します。
■4 Findメソッドについて
指摘し飽きましたけど、LookIn や LookAtが省略されてますね。大丈夫ですか？
また、見つからなかった場合↓みたいになり、実行時エラーになりますけど大丈夫ですか？
月末日 - Nothing.Column
■5 SearchDirectionについて
ただの興味本位で聞きますが、なんでSearchDirectionを指定したのですか？
SearchDirection 前方に検索(xlNext：既定値)、後方に検索(xlPrevious)[省略可能]
（もこな２） 2019/09/30(月) 05:22
■2
これに関してはちょっと簡略に出来そうなので考え直してきます。
■3
これに関しては実行月の月末日が出ると思っております。
月末日 = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "d") + 7
これで今月末の最終日になります。
7を足してる理由は、30日だと列が37番目、31日だと38番目になるためです。
■4
これに関してですが、”公”が無いってことは休みが無いって事だと思います。
さすがにそれはないので問題無いと思ってるのですが、やはり必要なんでしょうか？
■5
これはネットからコピペして上手くいったのもので、一番右の”公”を探してると思っております。
（なのれい） 2019/09/30(月) 07:18
0の場合は1〜3まで出
1の場合は1〜2まで出
2の場合は1が出
にしたいので提示頂いたものですと、2日、3日のい処理が出来ないと思います。
（なのれい） 2019/09/30(月) 08:31
氏名最終行 = Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "d") + 7
Range("D7:L" & 氏名最終行).Clear
For i = 7 To 氏名最終行
Cells(i, 7).Value = 月末日 - Range(i & ":" & i).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Column
If Cells(i, 月末日) = "B" Then
Cells(i, 8).Value = "出"
End If
If Cells(i, 7) <= 2 Then
Cells(i, 8).Value = "出"
If Cells(i, 7) <= 1 Then
Cells(i, 9).Value = "出"
If Cells(i, 7) <= 0 Then
Cells(i, 10).Value = "出"
End If
End If
End If
Next i
If Range("D4").Value < 12 Then
Range("D4") = Range("D4") + 1
Else
Range("D4") = 1
Range("C4") = Range("C4") + 1
End If
Range("L7:AL" & 氏名最終行).Clear
End Sub
現在修正かけて上記のコードになっております。
（なのれい） 2019/09/30(月) 08:42
氏名最終行 = Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "d") + 7
Range("D7:L" & 氏名最終行).Clear
For i = 7 To 氏名最終行
Cells(i, 7).Value = 月末日 - Range(i & ":" & i).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Column
If Cells(i, 月末日) = "B" Then
Cells(i, 8).Value = "出"
End If
Next i
If Range("D4").Value < 12 Then
Range("D4") = Range("D4") + 1
Else
Range("D4") = 1
Range("C4") = Range("C4") + 1
End If
Range("L7:AL" & 氏名最終行).Clear
End Sub
何度も申し訳ありません。消した場所あるんですが、当てはまらない従業員がいたので後に組み込む予定です。
とりあえず、上記でいかがでしょうか？
（なのれい） 2019/09/30(月) 09:49
With Worksheets("作成シート")
氏名最終行 = .Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "d") + 7
.Range("D7:L" & 氏名最終行).Clear
For i = 7 To 氏名最終行
.Cells(i, 7).Value = 月末日 - .Range(i & ":" & i).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Column
If .Cells(i, 月末日) = "B" Then
.Cells(i, 8).Value = "出"
End If
Next i
If .Range("D4").Value < 12 Then
.Range("D4") = Range("D4") + 1
Else
.Range("D4") = 1
.Range("C4") = Range("C4") + 1
End If
.Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
Withステートメントを使う場合はこうなるって事であってますか？
（なのれい） 2019/09/30(月) 10:06
年月の情報を入力したセルがあるのですから、year(date)やmonth(date)ではなくそちらを活かしてはいかがでしょうか。
「公」の検索対象範囲は最大でもH〜AM列でしょうから、行全体ではなくそれだけにしても良いのではないかと思います。
またみなさんご指摘ですが、検索の結果Nothingだったときの処理は必要だと思います。
（mori） 2019/09/30(月) 10:13
これに関して背景色が後々つくのでclearにしました。
年月の情報を入力したセルがあるのですから、year(date)やmonth(date)ではなくそちらを活かしてはいかがでしょうか。
これに関しては
月末日 = Format(DateSerial(.Range("C4"), .Range("D4") + 1, 0), "d") + 7
この様にしました。
「公」の検索対象範囲は最大でもH〜AM列でしょうから、行全体ではなくそれだけにしても良いのではないかと思います。
これに関しては
.Cells(i, 6).Value = 月末日 - .Range(Cells(i, 8), Cells(i, 月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Column
この様にしました。
.Range("D4") = Range("D4") + 1
.Range("C4") = Range("C4") + 1
「.」のつけ忘れはこの2つぐらいだと思います。
こちら付けました。
検索の結果Nothingだったときの処理は必要だと思います。
Option Explicit
Sub 月末連勤チェックと月初めシフト()
Dim 氏名最終行 As Long, 月末日 As Long, i As Long
Dim 最終公休日 As Range
With Worksheets("作成シート")
氏名最終行 = .Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(.Range("C4"), .Range("D4") + 1, 0), "d") + 7
.Range("D7:L" & 氏名最終行).Clear
.Range(Cells(7, 月末日), Cells(氏名最終行, 月末日)).Copy Range("G7")
For i = 7 To 氏名最終行
最終公休日 = .Range(Cells(i, 8), Cells(i, 月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious).Column
If Not (最終公休日 Is Nothing) Then
.Cells(i, 6).Value = 月末日 - 最終公休日
If Cells(i, 7) = "B" Then
Cells(i, 8).Value = "出"
End If
End If
Next i
If .Range("D4").Value < 12 Then
.Range("D4") = .Range("D4") + 1
Else
.Range("D4") = 1
.Range("C4") = .Range("C4") + 1
End If
.Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
何度もコード変わって申し訳ありません。
Nothingの処理はこんな感じにしないといけないとは思ってるんですが、やはり理解できない状態です。
すみません。
（なのれい） 2019/09/30(月) 11:25
変数「最終公休日」をrange型にするのであれば代入時に.columnを省いて
列の値として計算したいときに「最終公休日.column」とするか
変数「最終公休日」をlong型（またはinteger型等）にするのであれば
「not 最終公休日 is nothing 」という式ではなく違ったものにしなければなりません。
（mori） 2019/09/30(月) 12:15
With Worksheets("作成シート")
氏名最終行 = .Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(.Range("C4"), .Range("D4") + 1, 0), "d") + 7
.Range("D7:L" & 氏名最終行).Clear
.Range(Cells(7, 月末日), Cells(氏名最終行, 月末日)).Copy Range("G7")
For i = 7 To 氏名最終行
Set 最終公休日 = .Range(Cells(i, 8), Cells(i, 月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not (最終公休日 Is Nothing) Then
.Cells(i, 6).Value = 月末日 - Format(最終公休日.Column, "#,##0")
If .Cells(i, 7) = "B" Then
.Cells(i, 8).Value = "出"
End If
End If
Next i
If .Range("D4").Value < 12 Then
.Range("D4") = .Range("D4") + 1
Else
.Range("D4") = 1
.Range("C4") = .Range("C4") + 1
End If
.Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
こちらでいかがでしょうか？
一応問題なく処理はされています。
（なのれい） 2019/09/30(月) 13:00
お腹一杯かもしれませんが、
・ドットの漏れがまだたくさんあるようです。試しに"作成シート" 以外のシートを前面にして コードを実行してみてください。回答者の皆さんの指摘している意味がわかると思います。
・.Value を書いたり書かなかったり、統一したほうがいいと思います
・Findメソッドは Afterをきちんと指定しないと 誤動作するのでは？
・ .Cells(i, 6).Value = 月末日 - Format(最終公休日.Column, "#,##0") これは なぜFormatを使っているのですか？ 月末列 - 最終公休日セル.Column でいいと思います。
最後に
> If .Range("D4").Value < 12 Then
> .Range("D4") = .Range("D4") + 1
> Else
> .Range("D4") = 1
> .Range("C4") = .Range("C4") + 1
> End If
ですが .Range("D4").Value が 1 だった場合でも 最初の処理しか走りませんよ
（渡辺ひかる） 2019/09/30(月) 15:18
With Worksheets("作成シート")
氏名最終行 = .Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(.Range("C4"), .Range("D4") + 1, 0), "d") + 7
.Range("D7:L" & 氏名最終行).Clear
.Range(.Cells(7, 月末日), .Cells(氏名最終行, 月末日)).Copy .Range("G7")
For i = 7 To 氏名最終行
Set 最終公休日 = .Range(.Cells(i, 8), .Cells(i, 月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not (最終公休日 Is Nothing) Then
.Cells(i, 6).Value = 月末日 - 最終公休日.Column
If .Cells(i, 7).Value = "B" Then
.Cells(i, 8).Value = "出"
End If
End If
Next i
If .Range("D4").Value < 12 Then
.Range("D4").Value = .Range("D4") + 1
Else
.Range("D4").Value = 1
.Range("C4").Value = .Range("C4") + 1
End If
.Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
修正したコードです。
ドットの漏れの修正
.Valueの修正
月末列 - 最終公休日セル.Columnの修正
上記をしました。
If .Range("D4").Value < 12 Then
> .Range("D4") = .Range("D4") + 1
> Else
> .Range("D4") = 1
> .Range("C4") = .Range("C4") + 1
> End If
これはこのままの処理で良いと思いますけど、何かおかしいですか？
・Findメソッドは Afterをきちんと指定しないと 誤動作するのでは？
これは
.Find(What:="公", After:=Range("G" & i), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
.Find(What:="公", After:=Cells(i, 7), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
の様に変更してみましたが、型が一致しませんとなりました。
何がいけないんでしょうか？
（なのれい） 2019/09/30(月) 15:54
■6
表が見づらかったので整理してみました。
現状はこんな感じですよね
【作成シート】のレイアウト
1 2 3
____C______D____E___...___H____I____J____K_....__AG___AH___AI___AJ___AK___AL___
6 2019 10 月 1 2 3 4 26 27 28 29 30 31
7 火 水 木 金 土 日 月 火 水 木
8 名前 入替 B A 公 B B A
10 工藤 B B A A A 公
11 伊藤 A 公 B B B A
12 加藤 公 B A A 公 B
■7
＞これはネットからコピペして上手くいったのもので、一番右の”公”を探してると思っております。
＞これに関してですが、”公”が無いってことは休みが無いって事だと思います。
＞さすがにそれはないので問題無いと思ってる
なるほど、必ず検索範囲には「公」が1つ以上ある設計になっていて、複数ある場合は、一番最後（右）の「公」となっている"セル"を探したいのですね。
それなら、深く考えず元のまま行を丸ごと検索してしまえばよいとおもいます。（その場合、たぶんAfterは要らないです）
Sub 実験12()
Dim a As Range
Set a = Rows(12).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not a Is Nothing Then MsgBox "お探しのセルは【" & a.Address(0, 0) & "】です"
End Sub
■8
＞0の場合は1〜3まで出
＞1の場合は1〜2まで出
＞2の場合は1が出
＞にしたいので提示頂いたものですと、2日、3日のい処理が出来ないと思います。
なるほど。そういう処理がしたかったのですね。
実験8は私が処理を勘違いしていたので、確かに希望の動作にはならないですね。ごめんなさい。
現状でも、問題は無いと思いますが、【ブランクセルの時は処理しない】&【数字はそ0〜2のいずれかしか入らない】ということであれば、前回のようにSelect Caseを使って
Sub 実験9()
Const i As Long = 2
Select Case Cells(i, 6).Value
Case Is = "2": Application.Range(Cells(i, 8), Cells(i, 8)).Value = "出"
Case Is = "1": Application.Range(Cells(i, 8), Cells(i, 9)).Value = "出"
Case Is = "0": Application.Range(Cells(i, 8), Cells(i, 10)).Value = "出"
End Select
End Sub
とか、OffsetやResizeプロパティを使ってみるというアプローチもありそうです。
Sub 実験10()
Const i As Long = 2
If Cells(i, 6).Value <> "" And Cells(i, 6).Value >= 0 And Cells(i, 6).Value <= 2 Then
Application.Range(Cells(i, 8), Cells(i, 8).Offset(, 2 - Cells(i, 6).Value)).Value = "出"
End If
End Sub
Sub 実験11()
Const i As Long = 2
If Cells(i, 6).Value <> "" And Cells(i, 6).Value >= 0 And Cells(i, 6).Value <= 2 Then
Cells(i, 8).Resize(, 3 - Cells(i, 6).Value).Value = "出"
End If
End Sub
※実験9では「Case Is = 0」としてしまうと、ブランクセルも反応してしまうので意図的に文字列として判定しています。
（もこな２ ） 2019/09/30(月) 18:56
■7
これに関しては
Set 最終公休日 = .Range(.Cells(i, 8), .Cells(i, 月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not (最終公休日 Is Nothing) Then
この様にしております。
行全体を検索範囲にする必要が無いという指摘ありましたのでこの様にしています。
■8
これに関しては後々処理しますので、その時の参考にさせて頂きます。
ありがとうございます。
現在のコード
Option Explicit
Sub 月末連勤チェックと月初めシフト()
Dim 氏名最終行 As Long, 月末日 As Long, i As Long
Dim 最終公休日 As Range
With Worksheets("作成シート")
氏名最終行 = .Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(.Range("C4"), .Range("D4") + 1, 0), "d") + 7
.Range("D7:L" & 氏名最終行).Clear
.Range(.Cells(7, 月末日), .Cells(氏名最終行, 月末日)).Copy .Range("G7")
For i = 7 To 氏名最終行
Set 最終公休日 = .Range(.Cells(i, 8), .Cells(i, 月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not (最終公休日 Is Nothing) Then
.Cells(i, 6).Value = 月末日 - 最終公休日.Column
If .Cells(i, 7).Value = "B" Then
.Cells(i, 8).Value = "出"
End If
End If
Next i
If .Range("D4").Value < 12 Then
.Range("D4").Value = .Range("D4") + 1
Else
.Range("D4").Value = 1
.Range("C4").Value = .Range("C4") + 1
End If
.Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
（なのれい） 2019/09/30(月) 20:13
＞の様に変更してみましたが、型が一致しませんとなりました。 ＞ 何がいけないんでしょうか？
＞After:=Cells(i, 7)
Cellsの前のドットが抜けているとか？
こちらで試したところ コード自体は、問題ありませんでした
ただ、お詫びですが、私の認識に誤りがあり Afterを省略すると ActiveCellの次から検索すると思い込んでいたのですが ヘルプを見ると
「この引数を省略すると、対象セル範囲の左上端のセルが検索の開始点になります。」
とありましたので、今回の場合 検索範囲が一行だけで、左上端のセルから後方へ検索するので 結果として、最終公休日になるということですね。
ですから今回の場合は、省略しても問題はないようです。
それと
IF文については、私の勘違いでした すみません 条件文と処理文を取り違えていました。
（渡辺ひかる） 2019/10/01(火) 10:24
28 29 30 31 ← H列から何列目か
33 34 35 36 37 38 ← 列番号
____C______D____E___...___H___I___J___K___L__....__AG___AH___AI___AJ___AK___AL__ ← 列文字
4 2019 10 月 1 2 3 4 5 26 27 28 29 30 31
5 火 水 木 金 土 土 日 月 火 水 木
6 名前 入替 B A 公 B B A
7 工藤 B B A A A 公
8 伊藤 A 公 B B B A
9 加藤 公 B A A 公 B
■10
こちらも興味本位ですが、↓は何をしているのですか？
.Cells(i, 6).Value = 月末日 - 最終公休日.Column
たとえば、■9でいうと↓みたいになりますが・・・
E7 = 31+7-35 = 3 E8 = 31+7-38 = 0 E9 = 31+7-37 = 1
■11
年と月を入れる処理(月次更新）の部分は現状でも問題ないと思いますが、月末日をDate型として扱うなら↓のようにもできますね。
Sub 実験13()
Dim 月末日 As Date
月末日 = DateSerial(Range("C4"), Range("D4") + 1, 0)
MsgBox "今月の月末は" & Day(月末日) & "日です"
'月次更新
Range("C4").Value = Year(月末日 + 1)
Range("D4").Value = Month(月末日 + 1)
End Sub
■12
＞行全体を検索範囲にする必要が無いという指摘ありましたのでこの様にしています。
何度か言っていると思いますが、方法の一つを案内しているつもりですので、絶対この方法にすべきという意味ではありません。取捨選択はご自身で行ってください。
言われたまま（分からないけど）採用するとした場合、ご自身でメンテナンスできなくなりますから、分かる（理解できた）方法を採用すべきです。
ちなみに、■11のように月末日をDate型にした場合は、↓のようにH列から月末日までの分、列を拡張するという考え方もありますね。（前トピックで示してましたが）
Sub 実験14()
Dim 月末日 As Date
Dim 最終公休日 As Range
Const i As Long = 7
With ActiveSheet
月末日 = DateSerial(Range("C4"), Range("D4") + 1, 0)
Set 最終公休日 = .Cells(i, "H").Resize(, Day(月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not 最終公休日 Is Nothing Then MsgBox "最終公休日のセルは【" & 最終公休日.Address(0, 0) & "】です"
End With
End Sub
■13
質問とは関係ないですが、この掲示板に投稿する際に、行頭に1文字以上の半角スペースを入れると、ちょっと小さめの文字で改行されなくなります。
（逆に、半角スペースを入れた次の行は、前の行に依存するので行が変わって元のように表示したい場合は1行空ける必要が出てきちゃいますが）
↓半角スペースなし
ああああ
↓半角スペースあり
あああ
（もこな２） 2019/10/01(火) 12:25
■10
月末時点での出勤日数を確認しております。
月またぎでも7連勤務しない為の制御が必要と思い項目を設けております。
■11 ■12
こちらは現状で問題ないのであれば、このままいきたいと思います。
■13
わかりました。やってみます。
とりあえず、下記コードで完成で行きたいと思います。
Sub 月末連勤チェックと月初めシフト()
Dim 氏名最終行 As Long, 月末日 As Long, i As Long
Dim 最終公休日 As Range
With Worksheets("作成シート")
氏名最終行 = .Range("C6").End(xlDown).Row
月末日 = Format(DateSerial(.Range("C4"), .Range("D4") + 1, 0), "d") + 7
.Range("D6:L" & 氏名最終行).Clear
.Range(.Cells(7, 月末日), .Cells(氏名最終行, 月末日)).Copy .Range("G7")
For i = 7 To 氏名最終行
Set 最終公休日 = .Range(.Cells(i, 8), .Cells(i, 月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not (最終公休日 Is Nothing) Then
.Cells(i, 6).Value = 月末日 - 最終公休日.Column
If .Cells(i, 7).Value = "B" Then
.Cells(i, 8).Value = "出"
End If
End If
Next i
If .Range("D4").Value < 12 Then
.Range("D4").Value = .Range("D4") + 1
Else
.Range("D4").Value = 1
.Range("C4").Value = .Range("C4") + 1
End If
.Range("L7:AL" & 氏名最終行).Clear
End With
End Sub
皆様ありがとうございます。
まだ添削して頂きたいものがありますので、もし良ければ宜しくお願い致します。
（なのれい） 2019/10/01(火) 17:03
コンパイルエラーにならないことしかチェックしてないので雰囲気的なものと、インデント付けなどの参考にしてください。
Sub さんぷるM()
Dim 最終行 As Long, i As Long
Dim 月末日 As Date
Dim 最終公休日 As Range
With Worksheets("作成シート")
最終行 = .Cells(.Rows.Count, "D").End(xlUp).Row
If 最終行 <= 6 Then
MsgBox "データがありません"
Exit Sub
End If
月末日 = DateSerial(.Range("C4"), .Range("D4") + 1, 0)
'月前情報更新
.Range("D7", .Cells(最終行, "L")).Clear
.Range("H7", .Cells(最終行, "H").Offset(, Day(月末日) - 1)).Copy Range("G7")
'なんかの処理
For i = 7 To 最終行
Set 最終公休日 = .Cells(i, "H").Resize(, Day(月末日)).Find(What:="公", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not 最終公休日 Is Nothing Then
.Cells(i, 6).Value = Day(月末日) + 7 - 最終公休日.Column
If .Cells(i, 7).Value = "B" Then .Cells(i, 8).Value = "出"
End If
Next i
'月次処理
.Range("C4").Value = Year(月末日 + 1)
.Range("D4").Value = Month(月末日 + 1)
With .Range("H4")
.Resize(, 30).ClearContents
.NumberFormatLocal = "d"
.Value = 月末日 + 1
.AutoFill Destination:=.Resize(, Day(DateSerial(Year(月末日), Month(月末日) + 2, 0))), Type:=xlFillDefault
End With
'先月分情報クリア
.Range("L7", .Cells(最終行, "AL")).Clear
End With
End Sub
（もこな２ ） 2019/10/01(火) 18:30
提案頂いたコードをしっかり理解して自分のコードに組んで行きたいと思います。
いつもありがとうございます。
（なのれい） 2019/10/01(火) 19:13
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.