エクセル顧客管理 | 第34回.伝票番号の自動採番機能を追加 | Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材



最終更新日:2014-11-11

第34回.伝票番号の自動採番機能を追加


エクセルで顧客管理を作ろう、


前回までで「納品書」がほぼ完成しました、


概ね良さそうですが、伝票番号が直接入力しなければなりません。


別途に番号管理したのでは、なんだかなー・・・となってしまいます。



そこで、自動採番の機能を追加します。



手入力した場合は、それをそのまま使用して、


未入力の場合に、過去の最大番号を取得し、1アップした番号を作成します。


以下を、「Mod納品書」に追加します。

Function 納品書自動採番() As Variant
  Dim i1 As Long, i2 As Long
  Dim lngCol As Long
  Dim varAry As Variant
  Dim varMax As Variant
  Dim lngWork As Long
  
  With シート取得("売上明細")
    '伝票番号の最大値を検索
    i1 = .Cells(Rows.Count, 開始セル取得("売上明細").Column).End(xlUp).Row + 1
    lngCol = 売上明細登録列("納品書_伝票番号")
    varAry = .Range(.Cells(開始セル取得("売上明細").Row + 1, lngCol), .Cells(i1 - 1, lngCol))
    For i1 = LBound(varAry) To UBound(varAry)
      If varMax < varAry(i1, 1) Then
        varMax = varAry(i1, 1)
      End If
    Next
    If IsNumeric(varMax) Then '数値なら1アップ
      varMax = varMax + 1
      納品書自動採番 = varMax
      Exit Function
    End If
    For i1 = Len(varMax) To 1 Step -1 '後ろから数値以外を検索
      If Not IsNumeric(Mid(varMax, i1, 1)) Then
        i2 = i1
        Exit For
      End If
    Next
    If i2 >= Len(varMax) Then '数値が無い場合
      納品書自動採番 = ""
      Exit Function
    End If
    '数値部分のみ1アップ
    lngWork = CLng(Mid(varMax, i2 + 1)) + 1
    納品書自動採番 = Left(varMax, i2) & Format(lngWork, String(Len(varMax) - i2, "0"))
  End With
End Function


処理している事は、


伝票番号のデータを配列に入れる

配列から最大番号を検索する

最大番号が数値なら、1アップ→処理を抜ける

最大番号の後ろから数値以外を検索

最大番号が全て数値以外の場合→処理を抜ける

最大番号の後ろの数値部分のみ1アップする


今までに出てきた命令がほとんどですが、


CLng(文字列としての数値等)

文字列としての数値等を、長整数型(Long)へ変換します。


Format("数値・日付等","形式")

形式に従って、表示形式を設定します。

セルの書式設定と似た形式ですが、同じ出来ありません。

良く使う形式としては、

"yyyymmdd"

"yyyy/mm/dd"

"#,###"

"#,##0"

等になります。

ここでは、"00000"で、0の数を算出して指定しています。


String(数, 文字)

文字を数だけ並べた文字列を返します。


つまり、最大番号の後ろの数値桁数分の数値を作成しています。

AB00101→AB00102

のようにしています。


本来、採番は、決まりごとですので、このようなロジックはあまり書きません。


数値部分の桁数が決められていれば、単純にMid等で処理可能です。


上記処理なら、普通の番号は大抵処理可能だと思います。



さて、では、これを使用する部分の記述です。


納品書でF1が押下され、登録するモジュールの先頭に以下を追加します。


If IsEmpty(Range("納品書_伝票番号")) Then
  Range("納品書_伝票番号") = 納品書自動採番
  If IsEmpty(Range("納品書_伝票番号")) Then
    MsgBox ("伝票番号が指定されていません。" & vbLf & vbLf & _
        "自動での採番が出来ませんでした。")
    Exit Sub
  End If
  rtn = MsgBox("伝票番号を自動で採番しました。" & vbLf & vbLf & _
          "この伝票番号でよろしいですか。", vbYesNo, "確認")
End If


これは、見たとおりです。


伝票番号が未入力の場合に、先のFunctionを呼び、伝票番号を設定しています。


伝票番号が設定出来ない場合(一番最初の1件や、数値の無い番号)、


伝票番号の入力を促します。


入力忘れの場合もあるので、一応確認のメッセージで確認をしています。


完全に自動で採番する場合(特に数値のみの番号)は、確認が必要ないと思います。



ここで使用しているエクセルに大分プログラムを組みこんでしまいました。


「請求書」も入れました。


「請求書」は「納品書」とほぼ同様の機能です。


本来なら、複数「納品書」をまとめて請求する、総括請求書と内訳書、も作成したいところですが、


結構大変なので、とりあえず「納品書」の自動取り込みのみです。


従って、プログラムはほとんど「納品書」と同様なので、説明は割愛します。


次回は、メニューを作成し、アプリらしくしていきます。


そろそろ、完結に向かいます。






同じテーマ「エクセル顧客管理」の記事

第35回.メニューを作成
第36回.最終回
その後1、CSV出力を追加
その後2、ベクター掲載
第1回.どんなソフトにするか
第2回.顧客一覧のシートを作成
第3回.顧客登録のシートを作成、その前にマクロって何?

新着記事 ・・・新着記事一覧を見る

Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)
ファイルの一覧取得・削除(File)|Google Apps Script入門(1月24日)
フォルダの一覧取得・作成・削除(Folder)|Google Apps Script入門(1月24日)
フォルダとファイルを扱う(DriveApp)|Google Apps Script入門(1月24日)
スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.変数とデータ型(Dim)|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.VBAのFindメソッドの使い方には注意が必要です|ExcelマクロVBA技術解説



  • >
  • >
  • >
  • 伝票番号の自動採番機能を追加

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP