エクセル顧客管理
伝票番号の自動採番機能を追加

Excelマクロを駆使したカスタマイズ可能なエクセル顧客管理、エクセルVBAの学習教材
公開日:2013年5月以前 最終更新日: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件や、数値の無い番号)、


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


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


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



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


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


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


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


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


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


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


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





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

第28回.納品書データをデータベース化(1)

エクセルで顧客管理を作ろう、さーて、プログラム作成を進めます、シート「納品書」で入力した内容を、データベース化します。今回は、その前段作業を行います。シート「納品書」に名前定義を追加します。もう既に、多くの名前定義をしましたので、何が何やらわからなくなってしまっています。
第29回.納品書データをデータベース化(2)
エクセルで顧客管理を作ろう、前回の続きです、シート「納品書」で入力した内容を、データベース化します。前回作業で、「売上明細」の開始セルに名前定義を忘れていました。A3セルに「売上明細_開始」と名前定義してあります。
第30回.配列の使い方について
エクセルで顧客管理を作ろう、前回で、「売上明細」を作成しました、一部漏れもあったようで、消費税や総額が保存されていませんでした、作成方法は同じなので、名前定義の追加、VBAへの追記を同様にすれば良いでしょう。これから、「売上一覧」(伝票毎の合計一覧)を作成するのですが、プログラムを作成してみたところ、
第31回.売上一覧(伝票合計の一覧)を作成(1)
エクセルで顧客管理を作ろう、作成された、「売上明細」より、伝票合計の一覧を作成します、いろいろなやり方が考えられます、まずは、配列を使用せずに、エクセルの機能を使用してやってみます。どの程度のパフォーマンスが得られるかの実験です。次回は、配列を使っての処理をやります。
第32回.売上一覧(伝票合計の一覧)を作成(2)
エクセルで顧客管理を作ろう、作成された、「売上明細」より、伝票合計の一覧を作成します、前回に引き続き、3パターン目のやり方になります。今回のプログラムは難読ですよ。配列をいじくりまわしています。(笑) 先に、エクセルのサンプルをアップします。
第33回.売上一覧より納品書を作成
エクセルで顧客管理を作ろう、今回は、「売上一覧」で、行を選択し、F1押下で、該当の「納品書」を表示します。前回までですと、「納品書」は新規作成だけになっていましたので、これで、修正が可能になります。先に、エクセルのサンプルをアップします。
第34回.伝票番号の自動採番機能を追加
第35回.メニューを作成
エクセルで顧客管理を作ろう、エクセルも大分出来てきましたので、シート見出しを頼りに移動しているのは不便です、今回はメニューを作成します。以下が作成したメニューです。いろいろ、機能が増えていますが、プログラムは「納品書」とほぼ同様です。
第36回.最終回
エクセルで顧客管理を作ろう、今回で36回になりました、長い間お読み頂き感謝いたします、今回で、このシリーズは最終回といたします。本記事の最後で、現在のエクセルをダウンロードできます。当初の予定では、宛名や同報メールの機能も入れる予定でしたが、断念しました。
その後№1、CSV出力を追加
エクセルで顧客管理を作ろう、一旦終了しましたが、その後に機能追加していることについて、有益な部分をここで紹介します、説明は簡潔にしています、おそらくこれを読む人は、それなりにVBAをしっている方だとの判断です。今回はCSVの出力です。2通りの方法を紹介します。
その後№2、ベクター掲載
エクセルで顧客管理を作ろう、ここで作成したエクセルに、さらに機能追加しベクターに掲載しました。機能一覧 顧客登録 顧客一覧 商品マスタ 分類マスタ 納品書 売上一覧 請求書 請求書一覧 事跡登録 事跡一覧 メール送信 メール履歴 一覧にはCSV出力 ベクターへのリンク 「エクセル顧客管理」説明ページ:http://…


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

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.繰り返し処理(For Next)|VBA入門
6.RangeとCellsの使い方|VBA入門
7.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.並べ替え(Sort)|VBA入門
10.メッセージボックス(MsgBox関数)|VBA入門




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


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


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