Excel 現金出納帳で行を挿入して上の行と同じ数式を入れるマクロ(VBA)

シェアする

The following two tabs change content below.

松井 元(まつい はじめ)

静岡県三島市の松井会計事務所に勤務する理系税理士。 文理両方のスキルの融合を考えており、このブログは以下を中心に更新している。
●税金・会計に関すること
●IT(Excel、VBAなど)を使った業務効率化
●自分のこと(考え、私生活)

さらに詳細なプロフィールはこちら
松井会計事務所へのお仕事ご依頼・お問い合わせはこちら
税金・会計の無料メルマガはじめます

Excel では行を新たに挿入することができます。

行全体を選択した状態で「右クリック→行の挿入」を選択すればできます(ショートカットキーを使うと「Ctrl + プラスキー」)。

新たに挿入された行には数字、文字、数式は何も入っていません。

行の挿入

行を挿入する目的によっては、上の行と同じ情報を入れたい場合がありますよね。

特に数式。

今日は、新たに行を挿入する場合に、上の行と同じ数式を入れ込むマクロ(Excel VBA)を紹介します。

Excel で作った現金出納帳を題材にしますね!

レクタングル大 広告

1、Excel 現金出納帳への行の挿入

Excel 現金出納帳

以前に Excel 現金出納帳の作り方を紹介しました。

シートをこのように↓ アレンジしたものです。

Excel現金出納帳

日にち、勘定科目、摘要、収入金額、支払金額を左から右に順に入力していきます。

差引残高はセルに数式を入れて計算しています。

出納帳の外の余白の部分(I列)に数式を埋め込んで差引残高を計算しています。

現金出納帳の一番上の期首残高の部分↓

期首残高

期首残高の次の行以下は、同じ数式(行番号がずれていくだけ)を入れ込んであります。

差引残高

そして、出納帳の差引残高の列(F 列)は見栄えの関係で「収入金額」「支払金額」のいずれかが入力されているときだけ、差引残高を表示するようにしてあります。

差引残高

また、シート上で入力が必要な青色のセル以外は誤入力防止のため「シートの保護」をかけています。

入力に際し、TAB キーを押せばセルの選択を左から右側に移動できます。

一番右側(支払金額のセル)まできて TAB キーを押すと次の行に移ります。

TAB キーを押す

さて、このような現金出納帳にある程度入力をしていくと、このように↓ なります。

Excel現金出納帳入力

行の挿入

さて、現金出納帳にある程度入力した後に、追加で入力したくなるケースがあると思います。

行の挿入

そんなときには行を挿入するわけですが、以下の操作で行える通常の行の挿入

右クリック→行の挿入(ショートカットキーは「Ctrl + プラスキー」)

これだと行は挿入できても、数式は入ってきません。

自分で入力しなければならなくなります。

行を挿入した直後

行を挿入するたびに数式を自分で入れ込むのは大変なので、ここはマクロ(VBA)を使うべきですね。

仕組みを先にお話ししますと、

行のどこかを選択して、「1行挿入」ボタンをクリックすれば

上側に新しい行が挿入されて、かつ数式も入ってくるようにしてあります。

「1行挿入」ボタンにはマクロを埋め込んであります。

1行挿入マクロ

1行挿入マクロ

また、逆に行を削除したい場合には、削除したい行を選択して「1行削除」ボタンをクリックすれば削除できます。

「1行削除」ボタンにもマクロを埋め込んであります。

1行削除マクロ

1行削除マクロ

それぞれのボタンに埋め込んでマクロ

・1行挿入するマクロ

・1行削除するマクロ

について以下で説明しますね!

2、Excel 現金出納帳へ行を挿入するマクロの VBA コード

さて、まずは行を挿入するマクロの VBA コードから説明します。

「1行挿入」ボタンに組み込んであるマクロの分ですね。

全コード

全コードを以下に示します。

Row_Insert プロシージャに全て記述してあります。

コード説明

コードについて説明します。

変数の定義

シートの名前を入れる変数「Sheet_Name」を定義しています。

シート名の指定

シート名を変数「Sheet_Name」に入れます。

ActiveSheet.Name は ActiveSheet オブジェクトの Name プロパティです。

アクティブなシートのシート名を意味します。

これ↑ で変数「Sheet_Name」にアクティブなシートのシート名を入れています。

シートの保護、保護の解除

シートの保護をしてあるので、行を挿入する際にはいったん保護を解除しなければなりません。

そして、行を挿入した後に再度シートの保護をします。

パスワードを「123456」と設定しているものとします。

シート保護解除は Unprotect メソッドを使います。

次のように記述します。

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
*リンク先は私が勤務する松井会計事務所の HP です。

◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆経理業務効率化コンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

セミナー開催要望がありましたら、以下のリンク先にある個別のフォームよりお願い致します。
◆セミナー開催要望

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録
Worksheets(シート名).Unprotect Password:=パスワード

シート名は文字列で指定する場合は “” で囲います。

今回は変数で指定してあります。変数を “” で囲う必要はありません。

パスワードを指定するときは文字列を “” で囲います。

シート保護は Protect メソッドを使います。

次のように記述します。

Worksheets(シート名).Protect Password:=パスワード

シート名、パスワードについての必要事項は先の Unprotect メソッドと同じです。

行の挿入、上の行の特定の範囲をコピペ

行を挿入してその後に数式を入れ込む部分です。

数式の入れ込みは上の行の特定の範囲をコピペすることで対応しています。

図解すると、まず行を挿入します。

マクロで行を挿入

この部分は Insert メソッドを使っています。

次のように記述します。

オブジェクト.Insert

今回のように、オブジェクトとして行全体を指定すると、その行の下側に新しい行が挿入されます。

オブジェクトである行全体は、 WorkSheet オブジェクトの Rows プロパティを使って指定します。

Rows(行番号) 

このように記述すると「行番号」に該当する行全体をオブジェクトとして指定することになります。

Rows(1)  で1行目を意味します。

行番号を Selection.Row とすると、選択したセルの行番号を意味します。

ですので Rows(Selection.Row)とすることで、選択したセルと同じの行全体をオブジェクトとして指定しています。

そして

で選択したセルの上側の行に新しい行を挿入できます。

セルのコピー & ペースト

さて、新しい行を挿入しましたがそれだけではまだ不十分です。

・新たに挿入した行への数式の埋め込み

・ずれ込んだ行への数式の埋め込み

これらをやらなければなりません。

コピー&ペーストは以下のように記述して行います。

Range(範囲1).Copy Range(範囲2)

これで「範囲1」(セル)の内容をコピーして、「範囲2」(セル)に貼り付けます。

今回は、新たに挿入する行に1つ前の行の数式をコピペしたいわけです(普段の操作で言えば、セルを選択してコピペすれば数式も移ります)。

マクロで数式をコピペ

選択している行(Selection.Row)に新たに1行挿入されるので、その1行前(Selection.Row – 1)の内容の特定の範囲をコピペします。

セルで指定するときの列番号は F列〜I列(6 〜 9)とします。

これ↑ で、挿入した新しい行の6 〜 9列目に、その1つ上の行の 6 〜 9列目をコピペします。

マクロで数式をコピペ

これ↑ で、挿入した新しい行より1行下の6 〜 9列目に、挿入した新しい行より1つ上の行の 6 〜 9列目をコピペします(手作業でコピペするのと同じことになる)。

F列(6列目)、I列(9列目)に数式が入ってきます。

セルに数式が入る

セルに数式が入る

IF 文を使った場合分けで行を新たに挿入できる範囲を分ける

行を挿入しようとして選択しているセルの行番号が6以上の場合のみにマクロを実行するように IF 文を用いて場合分けしています。

(5行目以下の行は、それより上に入力用の行が無いから)

行番号が6以上の場合にマクロ実行

●選択している行の番号が6以上の場合、新たに行を挿入して数式をコピペ

●それ以外の場合「選択箇所では行の挿入はできません。」というメッセージを表示

このように↑しています。

コピーを解除

切り取り又はコピーの状態を解除するために、Application オブジェクトの CutCopyMode を False にします。

これで、コピーしている状態が解除されるのでコピーしている最中に出てくる点線を消すことができます。

3、Excel 現金出納帳の行を削除するマクロの VBA コード

次に、まずは行を削除するマクロの VBA コードから説明します。

「1行削除」ボタンに組み込んであるマクロの分ですね。

全コード

全コードを以下に示します。

Row_Delete プロシージャに全て記述してあります。

コード説明

基本的には、行を挿入する場合とプログラミングの流れは変わりません。

1点行を削除する部分について説明します。

Delete メソッドを使っています。

次のように記述します。

オブジェクト.Delete

今回のように、オブジェクトとして行全体を指定すると、その行が削除されます。

オブジェクトの指定の仕方は先ほどの Insert メソッドの場合と同じです。

で選択したセルの行が削除されます。

選択したセルの行を削除

4、まとめ

Excel 現金出納帳で行を挿入して上の行と同じ数式を入れるマクロ(VBA)を紹介しました。

行の挿入、範囲のコピペ という2つの作業の手間を省くことができます。

今回のようなシステムの中で特定のマクロを動かすためには、実行ボタンを作っておくと便利です。

Excel 現金出納帳の作り方についてはこちらの記事も参考にして下さい↓

お仕事のご依頼は、以下のリンク先にある個別のフォームよりお願い致します。
*リンク先は私が勤務する松井会計事務所の HP です。

◆税務・会計 顧問
◆税務・会計のスポットコンサルティング
◆経理業務効率化コンサルティング
◆Excel シートのオーダーメイド
◆Excel、マクロ(Excel VBA) のマンツーマンレッスン
◆東亜大学通信制大学院(法学専攻) 入試対策コンサル
◆その他のスポットコンサルティング(当ブログを読み何かご相談したい事項が出てきた方に向けたサービス)

セミナー開催要望がありましたら、以下のリンク先にある個別のフォームよりお願い致します。
◆セミナー開催要望

税金・会計についての無料メルマガをはじめます。配信をご希望させる方は以下のリンク先にあるフォームよりお願い致します
◆メルマガ登録
税理士試験まとめ、税法論文の書き方

————————————————

税理士試験についてのまとめはこちらです!

税法2科目免除大学院についてのまとめはこちらです!

kindle電子書籍を出版しました。

税理士試験の税法2科目免除のために大学院で税法論文(修士論文)を書いた経験を元に、大学院生としての目線から税法論文の書き方を体系化した書籍です↓

大学院での税法論文の書き方

本書籍の内容についてはこちらの記事をご確認下さい。

お問い合わせ・フォローボタン

———————————

よろしければフォロー下さい↓

Twitter フォローボタン:

Feedly フォローボタン:
follow us in feedly

Facebookページ:
*いいね!を押して頂くと更新情報が届きます。

レクタングル大 広告
レクタングル大 広告
関連コンテンツ



シェアする

フォローする