エクセルで列が自動挿入する明細書

質問

エクセルＶＢＡの勉強をしています。

見積明細書なのですが、横列は明細・数量・単位・単価・金額・備考の横並びセル群から成り、その明細行は下方へ数行繰り返されて、最後にそれらを合算する小計、消費税、合計金額行が来ます。

ここで聞きたいのが、数行程度の明細行＋小計・消費税・合計行の簡単なものを作っておいて、最後の明細行に記入すれば自動的にもう一行の空欄明細行が挿入されて、小計行以下は計算結果を表示しつつ自動的に一行繰り下がる、というものはできるのでしょうか。

回答No.3

merlionXX · ベストアンサー

yunako0517さん、こんにちは。merlionXXです。

１）について
ByVal とは値渡しをするというキーワードです。
Private Sub Worksheet_Change(ByVal Target As Range)は、Worksheetで Changeイベントが発生したときに自動的に実行されるプロシージャです。
イベント発生時にRange オブジェクト（変更先の範囲）が引数に渡り、それをがTarget という変数に格納されます。
自動マクロでの記録は標準モジュールですが、シートモジュールにはシートでイベント発生時に自動実行するマクロを記述するのでこのように書きます。

２）について
そうですか、明細欄に数式があるのですか・・・。
その数式がある金額欄がE列であり、１行目はタイトル行で2行目（E2セル）から数式があったとします。
以下のように変えてみてください。（各行ごとにコメントを付しておきました。コメントは’でコメントアウトしてありますのでこのままコピペしてもOKです。）

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'イベント発生を一時止める
On Error GoTo line 'エラーが発生したらline:へ飛ぶ
If Not Intersect(Target, Range("最終行").Offset(-1, 0)) Is Nothing Then '入力セルが「最終行」の1行上だったら以下を作動
Range("最終行").Offset(-1, 0).EntireRow.Insert '入力セルの上に1行挿入
Range("最終行").Offset(-2, 0).Value = Range("最終行").Offset(-1, 0).Value '挿入された行に入力値を代入
Range("最終行").Offset(-1, 0).ClearContents '値が重複する最終行の1行上をクリア
r = Range("最終行").Row - 1 '最終行の1行上の行番号取得しｒに代入
Range("E2").AutoFill Destination:=Range("E2:E" & r) 'E2セルから最終行の1行上までE2セルの式をAutoFill
End If
line:
Application.EnableEvents = True 'イベント発生を回復
End Sub

お礼

ありがとうございます！
できました～o(^ ^)o

１）についてもひとつひとつ単語の意味（値渡し？引数？変数に格納？などなど・・・）を調べつつ、何となくイメージでは分かったような…。

自分で応用して使いこなすのは、まだまだ先になりそうです(;^ ^)A
勉強します！
またよろしくお願いしますm(_ _)m

回答No.2

merlionXX

#1です。
やはり、常に二行あけるのもなんなのでもう一度考えてみました。
これならご希望どおり、最後の明細行に記入すれば自動的にもう一行の空欄明細行が挿入されて、小計行以下は計算結果を表示しつつ自動的に一行繰り下がるはずです。

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("最終行").Offset(-1, 0)) Is Nothing Then
With Application
.EnableEvents = False
Range("最終行").Offset(-1, 0).EntireRow.Insert
Range("最終行").Offset(-2, 0).Value = Range("最終行").Offset(-1, 0).Value
Range("最終行").Offset(-1, 0).ClearContents
.EnableEvents = True
End With
End If
End Sub

お礼

ありがとうございます！
こういうのを求めていました。
ただ、知識不足でコードの中身が分からないのですが、教えていただけるでしょうか？

１）１行目の(ByVal Target As Range)は何を表しているのですか？
　　自動マクロで作ったコードは、いつも『Sub マクロ名()』となっているので。

２）私の書き忘れなのですが、金額欄は数量×単価の数式が入っています。
　　この数式が入った行を挿入するには、どうすればよいのでしょう？

すみませんが、教えてください。

回答No.1

merlionXX

こんにちは、面白そうですね。
最終行の計算では当然それより上の行のセルを参照してるのですよね。
それならば、ご要望とはちょっと違いますが、最終行の２行上に入力があったら行が自動挿入され、最終行の参照範囲も1行分自動で広がる方法です。（つまり明細と小計の間には常に2行の空白行ができます）

それでよければ以下の手順をお試しください。

１．明細書が使っている範囲が仮に、明細・数量・単位・単価・金額・備考でA～F列としたら、最終行のA～F列に「名前の定義」で「最終行」と名前を付けてください。（行を挿入しても範囲を特定するためです。）

２．標準モジュールではなくシートモジュールに以下のコードをコピペします。

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("最終行").Offset(-2, 0)) Is Nothing Then
Range("最終行").Offset(-1, 0).EntireRow.Insert
End If
End Sub