金種表作成エクセルVBAの作り方②自動計算の金種数量表が作れる

kinsyusaku2eyecatch

こんにちは、じゅんぱ店長(@junpa33)です。

 

今回は、「金種表作成エクセルソフト」の作成で「金種・数量表示」のVBAコードの組み立てを行っていきます。

シートで言うと、「金種数量」シートの部分の説明となります。

自動計算で金種数量表が作れるVBAコードの作成です。

 

その前に、先回の記事をもう一度チェックするにはこちら↓になります。

エクセルVBA SUM関数を利用。金種数から合計金額を計算

 

金種表作成関係の記事はこちらです。

「金種表作成」の記事一覧を開く

 

金種数量表をデザインします

 

まず、どのような金種数量表(一覧表)にするかをプランします。

 

タテ列に相手先・項目を並べます。件数は制限を設定しない。

 

ヨコ列には金種ごとの数量を配置します。

 

合計欄は最終行に、縦割りで金種ごとに合計します。

 

シート名を変更します。

Sheet2は「金種数量」、Sheet3は「印刷用」としてください。

 

自動計算のVBAコード

 

作成する金種数量表はこのような感じです

 

kinsyu018a

 

VBAコードの作成

 

ここからのコードは、「Module2」に記述します。

 

モジュール内で利用する変数を宣言します

 

変数を宣言するコードです。モジュールの先頭に記述します。

Private SSheet As Worksheet, KSheet As Worksheet, ASheet As Worksheet

 

MEMO

宣言方法についてはこちらが参考になります。

エクセルVBA 変数の適用範囲を宣言方法でコントロールする

 

プロシージャー名「シート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

 

MEMO

シートのクリアについてはこちらが参考になります。

エクセルVBA シートのクリアーを目的のメソッド別に最速理解

 

コード本体のプロシージャー名は「金種数量」とします

 

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

 

MEMO

入力セルの最終行取得方法についてはこちらが参考になります。

エクセルVBA データ入力済セルの最終行番号を取得する方法

For ~ Nextについてはこちらが参考になります。

エクセルVBA!For~Nextの入れ子ループを最速に理解する

With ~ End Withについてはこちらが参考になります。

With~End Withの使い方。VBAコードを簡潔に記述する

 

次に金種名を表示しますが、 問題となる部分は、2000円を利用する場合としない場合があるということです。

先回解説の「2000円チェックボックス」の部分で説明いたしました様に、 「設定」シートのB1セルに「〇」が有るか無いかで「2000円」を使いかどうかを判定することが出来ます。

そこで、条件分岐で「〇」の場合「+1」、「 」の場合「±0」の値をセットします。

  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

 

MEMO

「For~Next」文の記述法についてはこちらが参考になります。

[エクセルVBA!For~Nextの入れ子ループを最速に理解する

「If条件文」についてはこちらが参考になります。

エクセルVBA 「If条件文」を最速に理解。条件の絞り方

 

金種ごとの枚数計算を行うコードです。

2000円を使いか使わないかの差は、K列が「金種1円か5円か」で判定します。

2000円を使う場合はK列が「5円」となります。

表の最終行に、「SUM関数」を利用して各タテ列の合計を集計表示します。

 

MEMO

Sum関数についてはこちらが参考になります。

エクセルVBA 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に記述したコードはこのようになります。

kinsyu026

 

kinsyu025a

 

これで、合計金額からどの金種がどれだけ必要かを、いちいち電卓を使って計算しなくても自動計算できるようになりました。

特に、複数の相手先がある場合には、効果絶大だと思います。

全体で準備する金種も合計欄の数量でOKということになります。

 

一応これで、VBAソフト作成の目的達成になりますが、

金種表の紙データを出力できるようにもしておきます。

(実際、紙化して保存という場合もまだまだ多いと思います。)

次回の記事で紹介させていただきます。

 

次回の記事に進むにはこちら↓になります。

金種表作成エクセルVBAの作り方③VBAで金種表を印刷する

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAを独学で習得するためのポイントは?良書との出会いは重要

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

エクセルVBAを使って業務効率を上げて行くのに、始めのうちに知っておきたいテーマを纏めてみました。

「VBA最速理解」の記事一覧を開く

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

 

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。