こんにちは、じゅんぱ店長（@junpa33）です。
今回は、「金種表作成エクセルソフト」の作成で「金種・数量表示」のＶＢＡコードの組み立てを行っていきます。
シートで言うと、「金種数量」シートの部分の説明となります。
自動計算で金種数量表が作れるＶＢＡコードの作成です。
その前に、先回の記事をもう一度チェックするにはこちら↓になります。
金種表作成関係の記事はこちらです。
コンテンツ
金種数量表をデザインします
まず、どのような金種数量表（一覧表）にするかをプランします。
タテ列に相手先・項目を並べます。件数は制限を設定しない。
ヨコ列には金種ごとの数量を配置します。
合計欄は最終行に、縦割りで金種ごとに合計します。
シート名を変更します。
Sheet2は「金種数量」、Sheet3は「印刷用」としてください。
自動計算のＶＢＡコード
作成する金種数量表はこのような感じです
ＶＢＡコードの作成
ここからのコードは、「Ｍｏｄｕｌｅ２」に記述します。
モジュール内で利用する変数を宣言します
変数を宣言するコードです。モジュールの先頭に記述します。
Private SSheet As Worksheet, KSheet As Worksheet, ASheet As Worksheet
プロシージャー名「シートSet」で変数を規定します。
Sub シートset()
Set SSheet = Worksheets(“設定”)
Set KSheet = Worksheets(“金種数量”)
Set ASheet = Worksheets(“印刷用”)
End Sub
シートの初期化のコードを記述します
Sub シートクリア()
With Cells
.ClearFormats
.ClearContents
.UseStandardHeight = True
.UseStandardWidth = True
End With
End Sub
コード本体のプロシージャー名は「金種数量」とします
Sub 金種数量()
End Sub
最初に「金種数量」シートを初期化して始めます。 ここのコードは、作表の中のセルの幅・高さ、フォントサイズやカラーなどを規定しています。
シートset
KSheet.Select
シートクリア
Dim Arow As Long, Brow As Long
Dim n As Long, s As Long, r As Long, t As Long
Dim V, Va, Vb, Vc, Vd, Ve, Vf, Vg, Vh, Vi
SSheet.Select
Arow = Cells(Rows.Count, 1).End(xlUp).Row
For n = 3 To Arow
KSheet.Range(“A” & n) = SSheet.Range(“A” & n)
KSheet.Range(“B” & n) = SSheet.Range(“B” & n)
Next n
KSheet.Range(“A” & Arow + 1) = “合計”
KSheet.Range(“B1”) = Now
KSheet.Select
With KSheet
.Rows.RowHeight = 21
.Range(“A:A”).Columns.ColumnWidth = 15
.Range(“B:B”).Columns.ColumnWidth = 13
.Range(“C:L”).Columns.ColumnWidth = 10
.Rows(1).RowHeight = 35
.Rows(2).RowHeight = 35
.Cells.Font.Size = 15
.Cells.Font.Bold = True
.Rows(1).Font.Size = 13
.Range(“B1”).Font.Size = 9
End With
Brow = Cells(Rows.Count, 1).End(xlUp).Row
Range(“B3”, “B” & Brow).NumberFormatLocal = “\#,##0;\-#,##0”
Range(“C1”, “L” & Brow).ClearContents
入力セルの最終行取得方法についてはこちらが参考になります。
For ~ Nextについてはこちらが参考になります。
エクセルVBA！For～Nextのループと入れ子構造をVBA最速理解
With ~ End Withについてはこちらが参考になります。
With～End Withの使い方。VBAコードを簡潔に記述する
セルのフォントについてはこの記事を参考にしてください。
次に金種名を表示しますが、 問題となる部分は、２０００円を利用する場合としない場合があるということです。
先回解説の「２０００円チェックボックス」の部分で説明いたしました様に、 「設定」シートのＢ１セルに「〇」が有るか無いかで「２０００円」を使いかどうかを判定することが出来ます。
そこで、条件分岐で「〇」の場合「＋１」、「 」の場合「±０」の値をセットします。
p = 0
Cells(1, 3) = 10000
Cells(1, 4) = 5000
If SSheet.Range(“B1”) = “〇” Then
p = 1
Cells(1, 5) = 2000
ElseIf SSheet.Range(“B1”) = “” Then
p = 0
End If
Cells(1, 5 + p) = 1000
Cells(1, 6 + p) = 500
Cells(1, 7 + p) = 100
Cells(1, 8 + p) = 50
Cells(1, 9 + p) = 10
Cells(1, 10 + p) = 5
Cells(1, 11 + p) = 1
Range(“C1:L1”).NumberFormatLocal = “#,##0円”
Range(“A1”, “L2”).HorizontalAlignment = xlCenter
For s = 3 To Brow Step 2
With Range(Cells(s, 1), Cells(s, 11 + p))
.Interior.ColorIndex = 20
End With
Next s
Range(Cells(Brow, 1), Cells(Brow, 11 + p)).Interior.ColorIndex = 6
For r = 1 To 12
If r = 1 Then
Cells(2, r) = “相手先・項目”
ElseIf r = 2 Then
Cells(2, r) = “金額”
Else
Cells(2, r) = “数量”
If Cells(1, 12) = “” Then
Cells(2, 12) = “”
End If
End If
Next r
「If条件文」についてはこちらが参考になります。
「If条件文」のVBAコードの組み方。条件の絞り方を最速に理解。
セルの表示形式についてはこちらを参考にしてください。
セルの背景色についてはこちらを参考にしてください。
金種ごとの枚数計算を行うコードです。
２０００円を使いか使わないかの差は、Ｋ列が「金種１円か５円か」で判定します。
２０００円を使う場合はＫ列が「５円」となります。
表の最終行に、「SUM関数」を利用して各タテ列の合計を集計表示します。
Sum関数についてはこちらが参考になります。
データのある最終列を調べるのにはこちらを参考にしてください。
KSheet.Select
For s = 3 To Brow – 1
V = Range(“B” & s).Value
Range(“C” & s) = V \ 10000
Va = V Mod 10000
Range(“D” & s) = Va \ 5000
Vb = V Mod 5000
Range(“E” & s) = Vb \ Range(“E1”)
Vc = Vb Mod Range(“E1”)
Range(“F” & s) = Vc \ Range(“F1”)
Vd = Vc Mod Range(“F1”)
Range(“G” & s) = Vd \ Range(“G1”)
Ve = Vd Mod Range(“G1”)
Range(“H” & s) = Ve \ Range(“H1”)
Vf = Ve Mod Range(“H1”)
Range(“I” & s) = Vf \ Range(“I1”)
Vg = Vf Mod Range(“I1”)
Range(“J” & s) = Vg \ Range(“J1”)
Vh = Vg Mod Range(“J1”)
If Range(“K1”) = 5 Then
Range(“K” & s) = Vh \ Range(“K1”)
Vi = Vh Mod Range(“K1”)
Range(“L” & s) = Vi
ElseIf Range(“K1”) = 1 Then
Range(“K” & s) = Vh
End If
Next s
Bcol = Cells(1, Columns.Count).End(xlToLeft).Column
For t = 2 To Bcol
Cells(Brow, t) = WorksheetFunction.Sum(Range(Cells(3, t), Cells(Brow – 1, t)))
Next t
ここまでのまとめ
Module2に記述したコードはこのようになります。
これで、合計金額からどの金種がどれだけ必要かを、いちいち電卓を使って計算しなくても自動計算できるようになりました。
特に、複数の相手先がある場合には、効果絶大だと思います。
全体で準備する金種も合計欄の数量でＯＫということになります。
一応これで、ＶＢＡソフト作成の目的達成になりますが、
金種表の紙データを出力できるようにもしておきます。
（実際、紙化して保存という場合もまだまだ多いと思います。）
次回の記事で紹介させていただきます。
次回の記事に進むにはこちら↓になります。
エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です
エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたいテーマを纏めてみました。
今回の記事はここまでです。 最後までご覧いただき有難うございました。