特定のセルに入力された日付が28日や30日などの「月末近く」である場合に定型データを入力するマクロを考えましたので備忘記録代わりに記事にしておこうと思います。
取引先への一括振込の管理業務をExcelでやっている場合には何か役にたつかもしれません。
目次
特定のセルに入力された日付が月末近くなら定型データを入力するマクロ
今回作成したマクロは下記のようなものになります↓
マクロのざっくりとした流れはこんな感じです↓
- B2セルに日付のデータが入力された場合に、その日付が月末近く（今回の場合は26日以降）である場合に
- メッセージボックスが表示され、定型データを追加するかどうかを確認するメッセージが表示される。
- 表示されたメッセージボックスで「OK」をクリックすると特定のセルにデータが入力される。
このマクロの使いどころですが、自分はこういった形の振込の管理業務シートで使用しています。
経理業務で振込業務をおこなっていると、例えば、月末だけ毎回同じ取引先に決まった金額を支払うということがあります。
具体的には今回のように月末に必ず「タイムパーキング」というところに「駐車場代を4万円支払う」といった形です。
このような月末に毎回定額の振込をする取引先について、振込忘れを防止するために今回マクロを作成しました。
マクロのコードはこんな感じになりました。
※2022年9月13日追記
以前公開していたコードの場合、日付を入力するセル（E2セル）が空欄の場合にもマクロが実行されることが判明したため、日付を入力するセルが空欄かどうかを判定させるコードを16行目に追加しました。
Private Sub Worksheet_Change(ByVal Target As Range)
'■B2セルに入力された日付が26日以降の場合に定型データを追加するマクロ
'■イベントプロシージャ版
'■変数宣言
Dim check As Long
'■処理実行部分
'rowとcolumnでイベントが発生するトリガーとなるセルを指定する
If Target.Row = 2 And Target.Column = 2 Then
If Target.Value<>"" and Day(Target.Value) >= 26 Then 'b2セルに入力された日付が空欄以外かつ26日以降なら
check = MsgBox("月末の支払いですが定型データを追加しますか？", vbYesNo + vbQuestion)
If check = vbYes Then
Range("b6").Value = "タイムパーキング駐車場代"
Range("c6").Value = 40000
End If
End If
End If
End Sub
なお、今回のマクロはイベントプロシージャを使用していますので上のコードの1行目がいつもと違う表示になっています。
イベントプロシージャを使うことでセルにデータが入力されたらマクロが実行されるように設定することができるようになります。
こうするとExcelの画面上にマクロを実行するボタンを配置する必要がなくなります。
イベントプロシージャの作り方はこちらの過去記事をご参考ください↓
特定のセルに入力された日付が月末近くなら定型データを入力するマクロの内容開設
今回作成したマクロについて内容をざっくりと説明しておきます。
特定のセルに入力されたかどうかを指定するにはrowとcolumnを使って指定する
今回B2セルに「何か」データが入力された場合にマクロが実行されるように設定する必要があります。
その場合には下記の赤枠で囲った箇所のように特定のセルを行と列で指定してあげる必要があります。
B2のセルは２行目にあり、かつ2列目にあるので、target.rowを２に、target.columnも２に設定してあげます。
もしC2セルにデータを入力するセルがあるのであればC2のセルは２行目にあり、かつ３列目にあるので、target.rowを２に、target.columnは３に設定することになります。
day関数を使って入力された日付データの「日」が何日か取得させる。
ここまでで、B2セルに何かデータが入力された場合にマクロが実行するように設定できました。
次に、セルに入力されたデータの日付が月末近くだったら定型データを追加するようにします。
これにはセルに入力された「年月日」の日付データから、「日」のデータを取得し、そのデータ月末近くかどうかを判定すればいいと思います。
そこで使っているの下記画像の赤枠部分のday関数です。
なお、今回は「日」のデータが26以上なら定型データを追加するようにしていますが26を書き換えて30日などとすることも可能です。
振込業務を考えた際に月末が土日などで休みの場合、休日前に振込処理を行う慣習があります。
そういった場合にも対応できるよう、きっちりと月末にせず、幅をもたせて月末近くにしています。
MsgBox関数を使って定型データを追加するか判断させる
MsgBox関数を使って画面上にメッセージを表示させ、定型データを追加するかどうかをユーザーに選択させるようにしています。
MsgBox関数の使いかたに関してはこちらの過去記事を参考にしていただければと↓
おまけ。ボタンで動かす場合のマクロのコード
今回作成したマクロをイベントプロシージャではなく、いつものようにExcelの画面上に設置したボタンを使って動かす場合のマクロのコードはこんな感じになります。
Excelの画面にボタンを設置してマクロを実行するほうがやりやすい方はこちらを参考にしてもらえたらと思います。
マクロを実行するのにクリックする必要がありますが、こちらのほうがコードの量も少なくなるのでスッキリして見やすいです。
Sub test()
'■B2セルに入力された日付が26日以降の場合に定型データを追加するマクロ（ボタン版）
'■変数宣言
Dim check As Long
'■処理実行部分
If Day(Range("b2").Value) >= 26 Then
check = MsgBox("月末の支払いですが定型データを追加しますか？", vbYesNo + vbQuestion)
If check = vbYes Then
Range("b6").Value = "タイムパーキング駐車場代"
Range("c6").Value = 40000
End If
End If
End Sub
まとめ
特定のセルに入力された日付が月末近くだったら定型データを入力するExcelマクロを考えました。
かなりニッチな記事だとは思いますが、似たような振込管理をExcelで行っている方は参考にしていただければと思います。
【本日の近況報告】
健康診断で胃カメラを受けてきました。午後からの検査だったため、朝食後は絶食となり、午後３時くらいまでずっと空腹状態でした。
胃カメラの前に口内を麻酔するために薬剤を口に含んでうがいするんですが、このうがい薬の苦いこと苦いこと。。。
【本日の１曲】
細野晴臣/恋は桃色
ラジオからこの曲が昼下がりに流れてきて、いい曲だなーと思って調べてアルバムを通して聴いてみたらこのアルバム、1枚通していいです。
アルバム全体に流れている「ゆる～い」空気感がこの暑い夏にぴったりです。
はっぴいえんどの風街ろまんが好きならこのアルバムも多分気にいるはず。知らんけど。
