Excel。マクロ020。小計行を挿入する時に注意しないといけない点
＜VBA＞
Excel VBAを少し知っているだけで、時短になるし、便利で快適になると思って入る人は多いのですが、
ハードルが高いと感じている事務職の方も多いようですね。
けど、それほど多くの構文（プログラム文）を書かなくても、
結構使えるExcel VBAはたくさんありますので、
今回は、小計行を挿入するExcel VBAを考えてみましょう。
次のような表があります。
四半期の小計行を挿入しようとすると、
行を挿入して、”小計”という文字を入力して、
SUM関数を使って範囲選択して合計を算出する作業が必要になります。
なかなか、面倒ですので、ここはExcel VBAの出番ですね。
ただ、少し注意しないといけないところがありますので、
確認しながら作成してみましょう。
Excel VBAの構文を、このように作ってみました。
Sub 四半期行挿入()
Dim i As Integer
For i = 5 To 13 Step 4
Rows(i).Insert
Cells(i, "a").Value = "小計"
Cells(i, "a").Interior.ThemeColor = xlThemeColorAccent6
Cells(i, "b").Formula = "=sum(b" & i - 3 & ":b" & i - 1 & ")"
Next
End Sub
構文を説明してみると、
Dim i As Integer
お馴染み、変数宣言ですね。整数型のIntegerを使って、変数iを用意しました。
For i = 5 To 13 Step 4 ～ Next
繰り返し処理をするための構造ですね。
5～13までStep 4、
つまり4行分加算しながら繰り返すということを行います。
Rows(i).Insert
これは行を挿入する処理をします。
Rows(i)なので、iが5ならば、Rows(5)なので5行目という意味になります。
Cells(i, "a").Value = "小計"
iが5の場合は、A5に小計という文字を入力します。
Cells(i, "a").Interior.ThemeColor = xlThemeColorAccent6
Interiorはセルを塗りつぶす処理をします。
そして、何色で塗りつぶすのか？というと、
ThemeColor = xlThemeColorAccent6
テーマの色の、アクセント6を使用しますという意味です。
これで、小計を入力した文字のセルに色を塗りつぶすことが出来ます。
Cells(i, "b").Formula = "=sum(b" & i - 3 & ":b" & i - 1 & ")"
iが5の場合は、B5に、Formula。つまり次の計算式を設定します。
その設定する計算式は、
“=sum(b" & i - 3 & ":b" & i - 1 & ")"
「&」で結合されているのでわかりにくいので、「&」を抜いてみると、
=sum(b2:b4)
という数式をB5に設定するという意味がわかります。
では、実行してみましょう。
小計行が挿入されて塗りつぶしもされて、
B列にも合計値が算出されているので、
完成しましたと言いたいところなのですが、12月の下に、小計行がありませんね。
ここが、今回のExcel VBAで少し注意しないといけない、
「ワナ」なんですね。
ポイントは、この行にあります。
For i = 5 To 13 Step 4
5行目から13行目までを処理するということなのですが、
行が挿入されてしまうので、最終行が13行目ではなくなってしまうのです。
なので、最終行を4プラスして、
17にすれば、12月の下の行に挿入することが出来るのですが、
イチイチ考えるのが大変ですね。
仮に、一行置きに挿入するとした場合、
何行増えるのかをデータのボリュームも含めて考えないといけません。
そこで、行を最終行から減らしていくようにすれば、わかりやすくなります。
では、構文をこのように変更してみましょう。
Sub 四半期行挿入後ろから()
Dim i As Integer
For i = 14 To 5 Step -3
Rows(i).Insert
Cells(i, "a").Value = "小計"
Cells(i, "a").Interior.ThemeColor = xlThemeColorAccent6
Cells(i, "b").Formula = "=sum(b" & i - 3 & ":b" & i - 1 & ")"
Next
End Sub
変更したところは、
For i = 14 To 5 Step -3
14行目から5行目まで3行分減算していきながら繰り返す処理をさせます。
では、実行してみましょう。
このように、四半期ごとに小計行を挿入することが出来ましたね。
Excel VBAで行を挿入する場合には、
For To Nextは減算させると便利と覚えておくといいかもしれませんね。
