インターネットバンキングのデータをExcelで加工して、会計ソフトへインポートするということをよくやっているんですが、たまに、同じ会社から口座引落が2回以上されているケースでそれぞれの引落金額が違うケースがあります。
こんな時、引落金額に応じてそれぞれの仕訳データの摘要に記載する内容を変化させるマクロを考えてみました。
Select Caseステートメントを使ってやってみます。
引落金額に応じてそれぞれの仕訳データの摘要に記載する内容を変化させるマクロ
今回やりたいことはざっくりいうとこんな感じです。
具体的にこんなことがしたい。
・通帳から電気代やガス代、携帯電話代などの料金が口座引落により支払いがされている。
・本社のガス代と、工場のガス代がそれぞれ別で引落がされているが通帳の摘要欄には同じ電力会社が記載されている。
・そして引落金額はかならず本社が1万円未満で工場の引落金額は１万円以上になるという規則性がある。
こんなケースで、1万円未満の引落なら仕訳の摘要を「本社分 電気代」とする
１万円以上であれば「工場分 電気代」とする。
ということで仮の通帳データを用意してみました。
このデータの「ガス オオサカガス」のように、通帳の摘要は同じだが、引落金額が異なるケースがあるとします。
で、例えばこのうち、１万円未満については事務所のガス代に該当するので摘要を「ガス代 大阪ガス 販管費部門」としたい。
１万円をこえるガス代については工場で使用しているガス代に該当するので「ガス代 大阪ガス 製造部門」としたい。
そんな感じのことをやりたいんです。
MFクラウドやfreeeを使用すると今回の記事とまったく同じことが簡単にできますね。
マクロ実行するとこうなります。
早速ですが、今回のマクロ実行するとこんな感じになります。
引落金額で摘要を区分するので、金額を変更して再度マクロを実行すると摘要の記載が変更されるのを確認していただければ。
コードとコードの説明
今回作成したコードはこんな感じです。
Sub 引落金額で摘要記載を区分するマクロ()
'■変数の宣言
Dim rng As Range
Dim targetrng As Range
Set rng = Range("e:e").Find(what:="ガス オオサカガス", lookat:=xlWhole)
'■処理実行部分
'■摘要が無い場合のエラー処理
If rng Is Nothing Then
MsgBox "お探しの摘要はありません。"
Exit Sub
'■摘要がみつかった場合の処理
Else
Set targetrng = rng
'■引落金額による判定を行う。
Select Case rng.Offset(0, -1).Value
Case Is < 10000
rng.Offset(0, 3).Value = "ガス代 大阪ガス 販管費部門"
Case Is >= 10000
rng.Offset(0, 3).Value = "ガス代 大阪ガス 製造部門"
End Select
Do
Set rng = Range("e:e").FindNext(rng) '次の摘要を探す
If rng.Address = targetrng.Address Then
Exit Do
Else
Select Case rng.Offset(0, -1).Value
Case Is < 10000
rng.Offset(0, 3).Value = "ガス代 大阪ガス 販管費部門"
Case Is >= 10000
rng.Offset(0, 3).Value = "ガス代 大阪ガス 製造部門"
End Select
End If
Loop
End If
MsgBox "作業完了。"
End Sub
使用している主なコード？としては
- find
- findnext
- offset
- ifステートメント
- do loopステートメント
- select caseステートメント
このあたりが主なものになると思います。
このうち、findやfindnext、do loopステートメントについては過去記事とまったく同じ使いかたをしていますのでそちらの記事を参考にしていただければ。
コードについて、完全な使いまわしです。究極の一本調子とでもいいましょうか・・
なので、select caseステートメントの部分だけ簡単に説明をしておきます。この部分です。
31行目 Select Case rng.Offset(0, -1).Valueの部分で、摘要欄「ガス オオサカガス」から左に１つ移動したセルである「出金欄」に記載された金額で判定を行うという指示を表現しています。
あとは33行目にてその金額（出金欄に記載された金額）が1万円未満であれば摘要欄「ガス オオサカガス」から右に３つ移動したセルへ「ガス代 大阪ガス 販管費部門」と記載する。
37行目にてその金額（出金欄に記載された金額）が1万円以上であれば摘要欄「ガス オオサカガス」から右に３つ移動したセルへ「ガス代 大阪ガス 製造部門」と記載する。
と指示しています。
なので33行目の10000を5000に変更してマクロを実行した場合は「出金欄」に記載された金額が5000円未満であれば摘要欄「ガス オオサカガス」から右に３つ移動したセルへ「ガス代 大阪ガス 販管費部門」と記載されることになります。
まとめ
インターネットバンキングで異なる金額で同じ会社から２回以上口座引落される場合等に引落金額で摘要を変えるマクロを考えてみました。
同じ処理についてはifステートメントでも可能だと思いますが、今回はSelect Caseステートメントの方が見やすいかな。と思いましたのでこちらを使用しました。
【本日の近況報告】
使用している弥生給与に不具合が続出しています。
具体的には年末調整業務に関して、当初設定している扶養情報と異なる控除額が算出される現象が頻発しています。
最終的に手計算でのチェックをかけるので大事にいたらずに済んでいますが、勘弁してほしいです。
【本日の１曲】
Letters To Cleo / I Want You To Want me
チープトリックの名曲「i want you to want me」のカバー。
映画「10 things i hate about you」のサントラに収録されています。
チープトリックのオリジナルの曲をテンポアップして、ボーカルも女性になっています。
個人的にThe！ガールズロック！って感じで好きです。
ちなみに映画はみたことはありません。
