[[20190121075954]] 『(マクロ)土日祝日リストを読み込む』(マイン) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『(マクロ)土日祝日リストを読み込む』(マイン)

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

勤務表の個人勤務入力行範囲に「土日祝日」の勤務を

作業セルにある祝日一覧(土日と祝日をリスト化したもの)を参照し
土日祝日の場合は勤務入力セルに「○」を表示させたいと考えております。

現在は、作業セル行を準備し、そこへ祝日一覧から土日祝日の日付を表示させ、単にコピペ(値貼り付け)で対応しております。

・カレンダー部分

 E5:AI5に日付が表示されています。

・土日祝日リスト部分(名前定義:祝日リスト)

 シート名(祝日)
 データ範囲A列:最終行

・作業セル

 E70:AI70に関数をセット
 =IFERROR(VLOOKUP(E5,祝日リスト,1,FALSE),"")

この作業セルに関数をセットせずにマクロ内で

・各個人行を選択→実行→土日祝日の日付が転記

といった処理ができないか考えております。

※祝日リストに「祝日」だけでなく「土日」もリストに加えていますが

できれば

「土日」は、カレンダー部分から読み取り
「祝日」は祝日一覧から読み込むといった形にできればなおありがたいです。

皆さま、どうかアドバイスの程よろしくおねがいします。

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 >・各個人行を選択→実行→土日祝日の日付が転記 

 ここがよく分からないです。

 1.個人行はどこにあるんですか?(E6:AI69 ですか?)

 2.個人毎に、一行ずつ転記するんですか?(人員数と同じ回数実行?)
   それとも、全員一気に転記 するんですか?(1回の実行で全員の行に埋める?)

 3.日付の転記じゃなく、該当する日に「○」を入力ですよね?

(半平太) 2019/01/21(月) 09:10


半平太さん

ありがとうございます。説明不足すみません。

1 個人行

7行目〜60行目の勤務入力範囲は「E列:AI列」

2 転記方法

個人ごとです。※ループ処理はアドバイスを参考に作成しようと考えています。

3 該当する日に「○」を入力

現在のコードです。

        '★処理範囲の設定=現在選択しているセル行を取得し転記
        Dim r As Long
        r = ActiveCell.row
        Range(Cells(r, 5), Cells(r, col)).Value = Range(Cells(70, 5), Cells(70, col)).Value

の部分で転記させています。

Sub 休日展開()

    AppActivate Application.Caption    'フォーカスをセルに移す(カーソル操作可能)
    Dim sh1 As Worksheet: Set sh1 = ActiveSheet
    '★エラー処理:指定シート以外では動かさない
    If Range("U1").Value <> "勤務表" Then  '勤務表を表示させているかの判定
        MsgBox ("このシートでは処理できません。勤務表でのみ有効です")
    Else
        '★最終行列の取得
        With sh1
            '最終列取得
            Dim col As Long
            On Error Resume Next    '念のため
            'AJ=4行目の36列目から左に検索(4行目には曜日番号がある)
            For col = Cells(4, 36).End(xlToLeft).Column To 1 Step -1
                'セルを順次左方向に値が""以外のセルに到達したら終了
                If Cells(4, col) <> "" Then Exit For
            Next col
            '最終行取得
            Dim row As Long
            On Error Resume Next    '念のため
            For row = Cells(Rows.Count, "B").End(xlUp).row To 1 Step -1
                If Cells(row, "B") <> "" Then Exit For
            Next row
        End With

        '★処理範囲の設定=現在選択しているセル行を取得し転記
        Dim r As Long
        r = ActiveCell.row
        Range(Cells(r, 5), Cells(r, col)).Value = Range(Cells(70, 5), Cells(70, col)).Value
    End If    '勤務表シート以外は終了
End Sub
(マイン) 2019/01/21(月) 10:20

 >        '★処理範囲の設定=現在選択しているセル行を取得し転記
 >        Dim r As Long
 >        r = ActiveCell.row
 >        Range(Cells(r, 5), Cells(r, col)).Value = Range(Cells(70, 5), Cells(70, col)).Value
 >    End If    '勤務表シート以外は終了
 >End Sub

 一行変更して、Function「holidays」を一つ追加する

 ↓
         '★処理範囲の設定=現在選択しているセル行を取得し転記        
         Dim r As Long        
         r = ActiveCell.row
         Range(Cells(r, 5), Cells(r, col)).Value = holidays(col) ’←1行変更
     End If    '勤務表シート以外は終了
 End Sub

 Private Function holidays(col As Long) ’←追加
     Dim it As Date
     Dim HLD(1 To 31) As String
     Dim CL As Long

     For CL = 1 To col - 4
         it = Cells(5, CL + 4).Value
         If Application.NetworkDays(it, it, Range("祝日リスト")) = 0 Then
             HLD(CL) = "○"
         End If
     Next CL
     holidays = HLD
 End Function

(半平太) 2019/01/21(月) 11:46


 単に一行だけの変更でも良かったです。(Function不要)

 >Range(Cells(r, 5), Cells(r, col)).Value = Range(Cells(70, 5), Cells(70, col)).Value
  ↓
   Range(Cells(r, 5), Cells(r, col)).Value = sh1.[INDEX(REPT("○",WORKDAY(E5:AD5-1,1,祝日リスト)>E5:AD5),1,0)]

(半平太) 2019/01/21(月) 14:33


半平太さま

遅くなり申し訳ございません。

ユーザー定義関数を本体に組み込む作業を行っていました。
無事に動き、配列の勉強にもなりました。

ありがとうございます。

リピート関数は初めて知りました。
なんで、こうなるかまだ理解できていませんが参考にさせていただきます。

現在、配列内に2列分取り込んでそれを参照するところに挑んでいます。
また改めて質問をさせていただきたいと思います。

本件は解決いたしました。

ありがとうございます。
(マイン) 2019/01/23(水) 13:54


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.