Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4256 / 13646 ツリー ←次へ | 前へ→

【57592】勤怠の自動チェック方法について質問です。 はると 08/9/3(水) 17:36 質問[未読]
【57593】Re:勤怠の自動チェック方法について質問で... kanabun 08/9/3(水) 18:56 発言[未読]
【57594】Re:勤怠の自動チェック方法について質問で... はると 08/9/3(水) 21:02 発言[未読]
【57599】Re:勤怠の自動チェック方法について質問で... こたつねこ 08/9/3(水) 22:26 回答[未読]
【57600】Re:勤怠の自動チェック方法について質問で... はると 08/9/4(木) 0:31 発言[未読]
【57601】Re:勤怠の自動チェック方法について質問で... かみちゃん 08/9/4(木) 5:33 発言[未読]
【57611】Re:勤怠の自動チェック方法について質問で... こたつねこ 08/9/4(木) 21:30 回答[未読]
【57622】Re:勤怠の自動チェック方法について質問で... はると 08/9/5(金) 13:30 発言[未読]
【57627】Re:勤怠の自動チェック方法について質問で... こたつねこ 08/9/5(金) 23:01 回答[未読]
【57635】Re:勤怠の自動チェック方法について質問で... はると 08/9/6(土) 11:10 質問[未読]
【57639】Re:勤怠の自動チェック方法について質問で... こたつねこ 08/9/6(土) 12:53 発言[未読]
【57643】Re:勤怠の自動チェック方法について質問で... はると 08/9/6(土) 17:38 発言[未読]
【57647】Re:勤怠の自動チェック方法について質問で... こたつねこ 08/9/6(土) 19:53 発言[未読]
【57655】Re:勤怠の自動チェック方法について質問で... はると 08/9/7(日) 16:05 発言[未読]
【57656】Re:勤怠の自動チェック方法について質問で... かみちゃん 08/9/7(日) 16:12 発言[未読]
【57664】Re:勤怠の自動チェック方法について質問で... こたつねこ 08/9/7(日) 22:38 発言[未読]
【57669】Re:勤怠の自動チェック方法について質問で... はると 08/9/8(月) 13:02 お礼[未読]

【57592】勤怠の自動チェック方法について質問です...
質問  はると  - 08/9/3(水) 17:36 -

引用なし
パスワード
   当方、勤怠をExcelで作成しています。
現在は、システムを入れていないために手作業でチェックを実施しています。

業務負荷を軽減したく、自動チェックできないか検討してみたのですが
何分知識不足とVBA未経験の為、お手上げ状態となっております。

どうか皆様のお力をお借りできませんでしょうか。

<質問内容>
現在、使用中のExcelは次のようなものを使用中です。

横に以下の項目を取ってあります。
日付 曜日 始業時間 終業時間 休憩開始 休憩終了 休憩時間 勤務時間 出社時間 退社時間  

縦には、それぞれの時間が入っております。

自動でチェックしたいのは、以下の3点です。
・出社時間が始業時間より30分以上遅くないか。
 (例:出社時間9:30 始業時間:9:00の場合はNG)
・退社時間が終業時間より30分以上早くないか。
 (例:退社時間17:30 終業時間18:00の場合はNG)
・終業時間が18:00以降の場合に、休憩時間17:45〜18:00が入力されているか。

情報が不足している部分があるかとは思いますが、
お力をお貸し頂けませんでしょうか。
宜しくお願い致します。

【57593】Re:勤怠の自動チェック方法について質問...
発言  kanabun  - 08/9/3(水) 18:56 -

引用なし
パスワード
   ▼はると さん:

>自動でチェックしたいのは、以下の3点です。
>・出社時間が始業時間より30分以上遅くないか。
> (例:出社時間9:30 始業時間:9:00の場合はNG)

作業列に
  =IF((出社時間-始業時間)<TimeValue("00:30:00"),"OK","NG")

以下同様、

では?

【57594】Re:勤怠の自動チェック方法について質問...
発言  はると  - 08/9/3(水) 21:02 -

引用なし
パスワード
   ▼kanabun さん:
>作業列に
>  =IF((出社時間-始業時間)<TimeValue("00:30:00"),"OK","NG")
>
>以下同様、
>
>では?

説明不足で申し訳ありません。
上げさせて頂いた3つの処理を1つのボタンを押すことで
チェックできるようなものを作成したいと考えております。

【57599】Re:勤怠の自動チェック方法について質問...
回答  こたつねこ  - 08/9/3(水) 22:26 -

引用なし
パスワード
   はるとさん、こんばんは

とりあえず作ってみました。
日付をまたがる様な処理は考えていません。
シート名【勤怠】の1行目にヘッダ、2行目以降データが記述され
各時間は時刻型で入力されているものとして、多分こんな感じかと
標準モジュールにどぞ

Public Sub 勤怠チェック()
  '定数---シート名や列の場所が違うときはここを変更して!
  Const シート名 As String = "勤怠"
  Const 日付 As String = "A"
  Const 出社時間列 As String = "I"
  Const 始業時間列 As String = "C"
  Const 退社時間列 As String = "J"
  Const 終業時間列 As String = "D"
  Const 休憩開始列 As String = "E"
  Const 休憩終了列 As String = "F"
  Const 遅刻許容 As Date = #12:30:00 AM#
  Const 早退許容 As Date = #12:30:00 AM#
  Const 休憩チェック As Date = #6:00:00 PM#
  Const 休憩開始時間 As Date = #5:45:00 PM#
  Const 休憩終了時間 As Date = #6:00:00 PM#
  
  Dim SHT As Excel.Worksheet
  Dim StartRow As Long
  Dim EndRow As Long
  Dim iii As Long
  
  StartRow = 2  'データ開始行は2行目から
  Set SHT = ThisWorkbook.Sheets(シート名)
  EndRow = SHT.Range(日付 & "1").End(xlDown).Row
  
  For iii = StartRow To EndRow
    '出社時間チェック
    If (SHT.Range(始業時間列 & iii).Value + 遅刻許容 - SHT.Range(出社時間列 & iii).Value) <= 0 Then
      SHT.Range(出社時間列 & iii).Interior.Color = vbRed
    End If
    
    '退社時間チェック
    If (SHT.Range(終業時間列 & iii).Value - 早退許容 - SHT.Range(退社時間列 & iii).Value) >= 0 Then
      SHT.Range(退社時間列 & iii).Interior.Color = vbRed
    End If
    
    '休憩時間チェック
    If (休憩チェック <= SHT.Range(退社時間列 & iii).Value) Then
      If (SHT.Range(休憩開始列 & iii).Value <> 休憩開始時間) Then SHT.Range(休憩開始列 & iii).Interior.Color = vbRed
      If (SHT.Range(休憩終了列 & iii).Value <> 休憩終了時間) Then SHT.Range(休憩終了列 & iii).Interior.Color = vbRed
    End If
  Next
End Sub

【57600】Re:勤怠の自動チェック方法について質問...
発言  はると  - 08/9/4(木) 0:31 -

引用なし
パスワード
   こたつねこさん

ありがとうございます。
作成頂いたもので、実行はできたのですが
どうもチェックできているのかがわかりません。

誠に申し訳ありませんが、
エラーの場合にメッセージがでるようにはできますでしょうか?

<情報を追加させて頂きます>
A列:日付(数字)のみ
H列:始業時間
I列:終業時間
J列:休憩開始時間
K列:休憩終了時間
L列:休憩時間合計
T列:出社時間
U列:退社時間
・1日が5行あり、J列とK列以外は列ごとに結合されています。
 (10行目〜15行目までが10日のようなものです。)
・休憩開始と終了時間は5行のうち昼休みを1行目、17:45以降の休憩を2行目という形で入力しています。
・休憩時間以外は、結合された列の1行目に入力されています。

ご依頼ばかりで誠に申し訳ありませんが、お力をお貸しください。
よろしくお願いいたします。

【57601】Re:勤怠の自動チェック方法について質問...
発言  かみちゃん E-MAIL  - 08/9/4(木) 5:33 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>作成頂いたもので、実行はできたのですが
>どうもチェックできているのかがわかりません。
>
>誠に申し訳ありませんが、
>エラーの場合にメッセージがでるようにはできますでしょうか?

サンプルデータを示して、具体的なチェック内容を示されたらいかがでしょうか?
期待している結果が具体的に見えていないような感じがします。

【57611】Re:勤怠の自動チェック方法について質問...
回答  こたつねこ  - 08/9/4(木) 21:30 -

引用なし
パスワード
   はるとさん、こんばんは

仕事場の都合でなかなか返事ができない状況で申し訳ないです。

>・1日が5行あり、J列とK列以外は列ごとに結合されています。
> (10行目〜15行目までが10日のようなものです。)
>・休憩開始と終了時間は5行のうち昼休みを1行目、17:45以降の休憩を2行目という形で入力しています。
>・休憩時間以外は、結合された列の1行目に入力されています。
行単位でセルの結合をされているようなので、前のVBAではうまく動かないですね。

はるとさんのワークシートはこちらから見れませんので質問する際は、かみちゃん
さんのおっしゃるとおり、シートレイアウトやサンプルデータを提示したほうが、
手戻りが少なくていいと思います。

データの入力形式によってはうまく動かないかもしれませんが、こんな感じでどう
でしょうか?

Public Sub 勤怠チェック()
  '定数
  Const シート名 As String = "勤怠"
  Const 日付 As String = "A"
  Const 出社時間列 As String = "T"
  Const 始業時間列 As String = "H"
  Const 退社時間列 As String = "U"
  Const 終業時間列 As String = "I"
  Const 休憩開始列 As String = "J"
  Const 休憩終了列 As String = "K"
  Const 遅刻許容 As Date = #12:30:00 AM#
  Const 早退許容 As Date = #12:30:00 AM#
  Const 休憩チェック As Date = #6:00:00 PM#
  Const 休憩開始時間 As Date = #5:45:00 PM#
  Const 休憩終了時間 As Date = #6:00:00 PM#
  Const 一日の行数 As Long = 5
  
  Dim SHT As Excel.Worksheet
  Dim StartRow As Long
  Dim EndRow As Long
  Dim iii As Long
  Dim jjj As Long
  Dim blnCHECK As Boolean

  StartRow = 2  'データ開始行は2行目から
  Set SHT = ThisWorkbook.Sheets(シート名)
  EndRow = SHT.Range(日付 & "65536").End(xlUp).Row
  
  For iii = StartRow To EndRow Step 5
    '出社時間チェック
    If (SHT.Range(始業時間列 & iii).Value + 遅刻許容 - SHT.Range(出社時間列 & iii).Value) <= 0 Then
      SHT.Range(出社時間列 & iii).Interior.Color = vbRed
    End If
    
    '退社時間チェック
    If (SHT.Range(終業時間列 & iii).Value - 早退許容 - SHT.Range(退社時間列 & iii).Value) >= 0 Then
      SHT.Range(退社時間列 & iii).Interior.Color = vbRed
    End If
    
    '休憩時間チェック
    If (休憩チェック <= SHT.Range(退社時間列 & iii).Value) Then
      blnCHECK = True
      For jjj = 0 To 一日の行数 - 1
        If (SHT.Range(休憩開始列 & iii).Offset(jjj, 0).Value = 休憩開始時間) Then
          If (SHT.Range(休憩終了列 & iii).Offset(jjj, 0).Value = 休憩終了時間) Then
            blnCHECK = False
            Exit For
          End If
        End If
      Next
      
      If blnCHECK Then
        SHT.Range(休憩開始列 & iii & ":" & 休憩終了列 & iii + 一日の行数 - 1).Interior.Color = vbRed
      End If
    End If
  Next
End Sub

【57622】Re:勤怠の自動チェック方法について質問...
発言  はると  - 08/9/5(金) 13:30 -

引用なし
パスワード
   こたつねこ さん
かみちゃん さん

ありがとうございます。

ご指摘頂きました内容に則しているか不安ですが
シートレイアウトを記載させて頂きたいと思います。
項目は4行目、データは4行目以降に入力されています。
1日は5行から成り立っています。
※休憩開始と休憩終了と休憩時間のみ5行がわかれており、その他は結合されています。

 A  G    H     I      J     K      L       T      U
4 日 曜日 始業時刻 終業時刻 休憩開始 休憩終了 休憩時間 出社時刻 退社時刻
5 20 木  9:30    20:00   12:15   13:00   1:00    9:20    20:10
6                  17:45    18:00   0:25
7
8
9
10 21 金  9:00   18:15   12:15    13:00   1:00   8:45    18:15    
                   17:45    18:00   0:25        

上記レイアウトで皆様にお力をお貸し頂きたいのは以下の内容です。
・始業時刻と出社時刻の時間差が31分以上の時にアラームが出る
・同様に終業時刻と退社時刻の時間差が31分以上の時にアラームが出る
・終業時刻が17:45以降の場合に休憩時間の17:45-18:00が選択されていなければアラームが出る
・上記のチェックをボタンか何かを押すことで完結させたい。

ご助言頂いた内容に則しているかわかりませんが、皆様のお力をお貸し頂けませんでしょうか。
ご依頼ばかりで申し訳ありませんが、宜しくお願い致します。

【57627】Re:勤怠の自動チェック方法について質問...
回答  こたつねこ  - 08/9/5(金) 23:01 -

引用なし
パスワード
   はるとさん、こんばんは

前回のソースと差し替えてみてください。
シート名は、はるとさんのシート名に書き換えてくださいね。

Public Sub 勤怠チェック()
  '定数
  Const シート名 As String = "勤怠" '←ここを実際のシート名で書き換えてください。
  Const 日付 As String = "A"
  Const 出社時間列 As String = "T"
  Const 始業時間列 As String = "H"
  Const 退社時間列 As String = "U"
  Const 終業時間列 As String = "I"
  Const 休憩開始列 As String = "J"
  Const 休憩終了列 As String = "K"
  Const 遅刻許容 As Date = #12:30:00 AM#
  Const 早退許容 As Date = #12:30:00 AM#
  Const 休憩チェック As Date = #5:45:00 PM#
  Const 休憩開始時間 As Date = #5:45:00 PM#
  Const 休憩終了時間 As Date = #6:00:00 PM#
  Const 一日の行数 As Long = 5
  
  Dim SHT As Excel.Worksheet
  Dim StartRow As Long
  Dim EndRow As Long
  Dim iii As Long
  Dim jjj As Long
  Dim blnCHECK As Boolean
  
  StartRow = 5  'データ開始行は5行目から
  Set SHT = ThisWorkbook.Sheets(シート名)
  EndRow = SHT.Range(日付 & "65536").End(xlUp).Row
  
  For iii = StartRow To EndRow Step 一日の行数
    '出社時間チェック
    If (SHT.Range(始業時間列 & iii).Value + 遅刻許容 - SHT.Range(出社時間列 & iii).Value) < 0 Then
      SHT.Range(出社時間列 & iii).Interior.Color = vbRed
    End If
    
    '退社時間チェック
    If (SHT.Range(終業時間列 & iii).Value - 早退許容 - SHT.Range(退社時間列 & iii).Value) > 0 Then
      SHT.Range(退社時間列 & iii).Interior.Color = vbRed
    End If
    
    '休憩時間チェック
    If (休憩チェック <= SHT.Range(終業時間列 & iii).Value) Then
      blnCHECK = True
      For jjj = 0 To 一日の行数 - 1
        If (SHT.Range(休憩開始列 & iii).Offset(jjj, 0).Value = 休憩開始時間) Then
          If (SHT.Range(休憩終了列 & iii).Offset(jjj, 0).Value = 休憩終了時間) Then
            blnCHECK = False
            Exit For
          End If
        End If
      Next
      
      If blnCHECK Then
        SHT.Range(休憩開始列 & iii & ":" & 休憩終了列 & iii + 一日の行数 - 1).Interior.Color = vbRed
      End If
    End If
  Next
End Sub

【57635】Re:勤怠の自動チェック方法について質問...
質問  はると  - 08/9/6(土) 11:10 -

引用なし
パスワード
   こたつねこ さん:
お世話になっております。

早速作成頂き、ありがとうございます。
改めて私自身では作成できないと実感しております。

作成して頂いたコードを実行すると以下の箇所で
エラーが発生してしまいました。

申し訳ありませんが、対応策を御教授頂けますでしょうか。
よろしくお願いいたします。

<発生箇所>
If blnCHECK Then
        SHT.Range(休憩開始列 & iii & ":" & 休憩終了列 & iii + 一日の行数 - 1).Interior.Color = vbRed
      End If

<エラー内容>
実行時エラー'1004'
アプリケーション定義またはオブジェクト定義のエラーです。

【57639】Re:勤怠の自動チェック方法について質問...
発言  こたつねこ  - 08/9/6(土) 12:53 -

引用なし
パスワード
   はるとさん、こんにちは

><発生箇所>
> If blnCHECK Then
>        SHT.Range(休憩開始列 & iii & ":" & 休憩終了列 & iii + 一日の行数 - 1).Interior.Color = vbRed
>      End If
>
><エラー内容>
>実行時エラー'1004'
>アプリケーション定義またはオブジェクト定義のエラーです。
こちらの環境では、このエラー出ないんですよ・・・
ワークシートのレイアウトは前に提示された通りになっているんですよね?

今はるとさんが実際に実行しているマクロを提示していただけませんか?

【57643】Re:勤怠の自動チェック方法について質問...
発言  はると  - 08/9/6(土) 17:38 -

引用なし
パスワード
   こたつねこ さん

何度もご親切にお答え頂き、ありがとうございます。

>こちらの環境では、このエラー出ないんですよ・・・
>ワークシートのレイアウトは前に提示された通りになっているんですよね?


A.ワークシートは、以前ご提示させて頂いた通りですが、私も確認すればよかったのですが、前任者が作成したシートのため、保護がかかっており
保護を解除すれば実行できました。

>
>今はるとさんが実際に実行しているマクロを提示していただけませんか?
A.マクロは作成頂いたものをシート名だけ変更して使用しております。

わかりませんが、恐らく非表示になっていたX列〜AT列までにある
データのせいかもしれないと考えております。

Q.後から色々申し訳ありませんが、勤怠データは159行目までに入力されており、160行目からはセル結合などにより、合計値を表示させたりしておりますので、実際にチェックをかけるのは159行目までです。

お手数をお掛けして誠に申し訳ありません。
ご都合の良い時にでもお答えください。

【57647】Re:勤怠の自動チェック方法について質問...
発言  こたつねこ  - 08/9/6(土) 19:53 -

引用なし
パスワード
   はると さん、こんばんは

>Q.後から色々申し訳ありませんが、勤怠データは159行目までに入力されており、160行目からはセル結合などにより、合計値を表示させたりしておりますので、実際にチェックをかけるのは159行目までです。

であれば、
EndRow = SHT.Range(日付 & "65536").End(xlUp).Row

EndRow = 155
にすれば動きませんか?

【57655】Re:勤怠の自動チェック方法について質問...
発言  はると  - 08/9/7(日) 16:05 -

引用なし
パスワード
   こたつねこ さん

いつもお世話になっております。

ご教授頂いた方法で実行できました。

Q1.確認させて頂きたいのですが、複数シートがある場合でも実行可能なのでしょうか?

Q2.退社時刻と終業時刻の差が30分以上の時のチェック結果はどこで確認できるのでしょうか?

実行できたうえでの質問となり、お手数をお掛けいたしますが
よろしくお願いいたします。

遅くなりましたが、ここまでのご丁寧にお力をお貸し頂き
誠にありがとうございます。

【57656】Re:勤怠の自動チェック方法について質問...
発言  かみちゃん E-MAIL  - 08/9/7(日) 16:12 -

引用なし
パスワード
   こんにちは。かみちゃん です。

横から失礼します。

>Q1.確認させて頂きたいのですが、複数シートがある場合でも実行可能なのでしょうか?

こちらだけコメントさせていただきます。

以下のように修正し、Sampleマクロを実行することで可能です。

'Public Sub 勤怠チェック()
Public Sub 勤怠チェック(ws As Worksheet)
 Dim SHT As Worksheet
 
 'Set SHT = ThisWorkbook.Sheets(シート名)
 Set SHT = ws
End Sub

Sub Sample()
 Dim ws As Worksheet
 
 For Each ws In ThisWorkbook.Worksheets
  Call 勤怠チェック(ws)
 Next
End Sub

【57664】Re:勤怠の自動チェック方法について質問...
発言  こたつねこ  - 08/9/7(日) 22:38 -

引用なし
パスワード
   はると さん、かみちゃん さん、こんばんは

かみちゃん、フォローありがとうございます。

>Q2.退社時刻と終業時刻の差が30分以上の時のチェック結果はどこで確認で
>きるのでしょうか?
退社時間のセルの色が赤くなるはずです。
確認してみてください。

【57669】Re:勤怠の自動チェック方法について質問...
お礼  はると  - 08/9/8(月) 13:02 -

引用なし
パスワード
   こたつねこ さん かみちゃん さん

お世話になっております。

ご教授頂きました方法で実施したところ
無事に実行することが出来ました。

これもお二人にご協力頂けたからこそです。
私一人では完成することは出来なかったと
実感しております。

この度は、本当にありがとうございました。

4256 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free