第33回.売上一覧より納品書を作成
エクセルで顧客管理を作ろう、
今回は、「売上一覧」で、行を選択し、F1押下で、該当の「納品書」を表示します。
前回までですと、「納品書」は新規作成だけになっていましたので、
これで、修正が可能になります。
先に、エクセルのサンプル をアップします。
ダウンロード用の別サイトが表示されます。
まずは、「Mod共通」の「ファンクションF1」に以下を追加
Select Case True
Case ActiveSheet Is
シート取得("売上一覧")
If Not IsEmpty(Cells(ActiveCell.Row,
開始セル取得("売上一覧").Column)) And _
ActiveCell.Row > 開始セル取得("売上一覧").Row
Then
Range("納品書_伝票番号") = Cells(ActiveCell.Row, 開始セル取得("売上一覧").Column
+ 1)
If Cells(ActiveCell.Row, 開始セル取得("売上一覧").Column) = 0
Then
Range("納品書_削除").ClearContents
Else
Range("納品書_削除")
= Cells(ActiveCell.Row, 開始セル取得("売上一覧").Column)
End
If
シート取得("納品書").Select
Call 売上明細取得
End
If
このモジュールは何度も出てきています。
F1に機能を追加する場合は、まずこのモジュールに、当該シートの処理を追加します。
Range("納品書_削除")は、「納品書」の伝票番号下に追加した名前定義です。
「削除」が0以外、つまり修正前の伝票の場合は、修正不可にする為の仕掛けです。
次に、「Mod納品書」に以下を追加します。
Sub 売上明細取得()
Dim i1 As Long, i2 As Long
Dim lngRow As Long, lngCol
As Long
Dim lngCol1 As Long, lngCol2 As Long
Dim varAry1 As Variant,
varAry2 As Variant
Dim blnFirst As Boolean
Dim varNo As
Variant
Dim varDel As Variant
Call
マクロ開始処理
'明細行をクリアする
lngRow =
Range("納品書_商品番号").Rows.Count
Range(Range("納品書_商品番号").Cells(2, 1),
Range("納品書_商品番号").Cells(lngRow, 1)) = ""
Range(Range("納品書_商品名").Cells(2,
1), Range("納品書_商品名").Cells(lngRow, 1)) = ""
Range(Range("納品書_数量").Cells(2,
1), Range("納品書_数量").Cells(lngRow, 1)) = ""
Range(Range("納品書_単位").Cells(2,
1), Range("納品書_単位").Cells(lngRow, 1)) = ""
Range(Range("納品書_単価").Cells(2,
1), Range("納品書_単価").Cells(lngRow, 1)) =
""
' Range(Range("納品書_金額").Cells(2, 1),
Range("納品書_金額").Cells(lngRow, 1)) = ""
Range(Range("納品書_備考").Cells(2, 1),
Range("納品書_備考").Cells(lngRow, 1)) = ""
varNo =
Range("納品書_伝票番号")
varDel = Range("納品書_削除")
blnFirst = False
With
シート取得("売上明細")
'最終行を取得
i1 = .Cells(Rows.Count,
開始セル取得("売上明細").Column).End(xlUp).Row + 1
'伝票番号、削除で検索
lngCol1 =
登録列取得("納品書_削除")
lngCol2 = 登録列取得("納品書_伝票番号")
If lngCol2 >
lngCol1 Then
lngCol = lngCol2
Else
lngCol =
lngCol1
End If
varNo = Range("納品書_伝票番号").Value
varDel =
Range("納品書_削除").Value
varAry1 = .Range(.Cells(開始セル取得("売上明細").Row + 1,
lngCol1), .Cells(i1 - 1, lngCol1))
varAry2 =
.Range(.Cells(開始セル取得("売上明細").Row + 1, lngCol2), .Cells(i1 - 1,
lngCol2))
For i1 = LBound(varAry1) To UBound(varAry1)
If
varAry1(i1, 1) = varDel And _
varAry2(i1, 1) = varNo
Then
'明細行を取得
i2 = i1 + 開始セル取得("売上明細").Row
'売上明細の行数
lngCol = 登録列取得("納品書_行番号")
lngRow = .Cells(i2,
lngCol).Value + 1
Range("納品書_行番号").Cells(lngRow, 1) = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_商品番号")
Range("納品書_商品番号").Cells(lngRow, 1) = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_商品名")
Range("納品書_商品名").Cells(lngRow, 1) = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_数量")
Range("納品書_数量").Cells(lngRow, 1) = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_単位")
Range("納品書_単位").Cells(lngRow, 1) = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_単価")
Range("納品書_単価").Cells(lngRow, 1) = .Cells(i2,
lngCol)
' lngCol =
登録列取得("納品書_金額")
' Range("納品書_金額").Cells(lngRow, 1) =
.Cells(i2, lngCol)
lngCol =
登録列取得("納品書_備考")
Range("納品書_備考").Cells(lngRow, 1) = .Cells(i2,
lngCol)
If blnFirst = False Then
lngCol =
登録列取得("納品書_伝票番号")
Range("納品書_伝票番号").Value = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_納品日")
Range("納品書_納品日").Value = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_自社担当")
Range("納品書_自社担当").Value = .Cells(i2,
lngCol)
' lngCol =
登録列取得("納品書_税抜")
' Range("納品書_税抜").Value =
.Cells(i2, lngCol)
' lngCol =
登録列取得("納品書_消費税")
' Range("納品書_消費税").Value =
.Cells(i2, lngCol)
' lngCol =
登録列取得("納品書_総額")
' Range("納品書_総額").Value =
.Cells(i2, lngCol)
lngCol =
登録列取得("納品書_郵便番号")
Range("納品書_郵便番号").Value = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_住所1")
Range("納品書_住所1").Value = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_住所2")
Range("納品書_住所2").Value = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_顧客名")
Range("納品書_顧客名").Value = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_担当者名")
Range("納品書_担当者名").Value = .Cells(i2,
lngCol)
lngCol =
登録列取得("納品書_顧客番号")
Range("納品書_顧客番号").Value = .Cells(i2,
lngCol)
blnFirst = True
End If
End
If
Next
End With
Call マクロ終了処理
End
Sub
長いですね、でもほとんどは、個別の項目へのデータ設定です。
Function 登録列取得
を使用していますが、Subモジュール「売上明細へ登録」を作成した時に作ったものです。
このように、汎用性のあるFunctionを作っておくと、後々使う事が出来ます。
処理内容は、「売上明細へ登録」とほぼ同様です。
「売上明細へ登録」は、「納品書」→「売上明細」
「売上明細取得」は、「売上明細」→「納品書」
ですので、代入式の左右を入れ替えれば、ほぼ完成となっています。
もちろん、多少の修正は必要です。
全体の流れは、
明細行(商品の各行)をクリア
↓
「売上明細」の「削除」から「伝票番号」までを、配列に入れます。
この時、「削除」と「伝票番号」の列番号の大小比較をしています。
↓
配列を全行処理
↓
「削除」「伝票番号」が一致している場合
↓
「売上明細」→「納品書」を実行します。
最初の1件目の場合のみ、ヘッダー項目(顧客情報等)を設定
注意点としては、
For i1 = LBound(varAry1) To
UBound(varAry1)
の中で、途中でExitすることなく、最後まで処理しています。
一見無駄なようです、他の伝票になった時点でExitすれば良いように思えます。
しかし、エクセルはデータを使用者が自由に触る事が可能ですので、
ソート等をしていたりすると、伝票が、泣きわかれになっている場合もあります。
このような場合も想定して、1件残らず処理しています。
もちろん、先にソートしておく事も方法としては有効です。
さらに、先頭に「'」になっている行が多くあります。
これは、コメント行なのはおわかりだろうと思います。
では、何故、わざわざ残しているかが問題です。
読み手に対し、金額は計算式があるので、値は設定しませんと伝えています。
これがないと、読み手は、項目漏れがないか等が気になり、確認作業をしたりします。
ここでは、そんな心配はいりません、全項目書いてあります、と伝えています。
修正前の伝票、つまり「削除」が>1の伝票の場合は、修正不可にする為に、
Subモジュール「売上明細へ登録」の先頭に、以下を追加します。
If Range("納品書_削除") > 0 Then
MsgBox ("この伝票は修正され、最新の伝票ではありません。" &
vbLf & vbLf & _
"削除が0の伝票以外は修正登録できません。")
Exit Sub
End
If
これは、みた通りです、削除>0、つまり修正前の伝票の場合は、
メッセージを表示して、処理を抜けるようにしています。
そこで、何か無いかと思案して、セルのマージについて書きます。
結合セルの扱いについて
「売上明細取得」の最初の方で、
Range(Range("納品書_商品名").Cells(2, 1),
Range("納品書_商品名").Cells(lngRow, 1)) = ""
があります。
これを
Range(Range("納品書_商品名").Cells(2, 1), Range("納品書_商品名").Cells(lngRow, 1)).ClearContents
としたら、どうなるでしょうか。
「結合されたセルの一部を変更することはできません」と、エクセルに叱られます。
Range("納品書_商品名")は、4つのセルの結合になっているからです。
ではなぜ、「 = ""」は良いのでしょうか、少し疑問ですよね。
まず、ClearContentsはメソッドで、「 = ""」は、Valueプロパティへの値の代入です。
全く違うものですが、何故、ClearContentsがエラーを返しているのかの詳細は、私にはわかりません。
この場合の、正しい記述としては、.「MergeArea.ClearContents」となります。
ただ、値を消すだけなら、「 = ""」で十分ですよね。
.MergeArea
は、そのセルが含まれる結合セルの範囲(Rangeオブジェクト)を返す、プロパティです。
結合セルでも、大抵のプロパティは、個別に設定してもエラーにはなりません。
しかし、先頭セル以外は無視されます。
従って、結合セルの先頭に対して、指定する必要があります。
これは、値の取得時についても同じです、先頭セル以外は値を取得できません。
では、先頭セルの取得はどうすれば良いでしょうか。
ネットを少し検索したのですが、掲示板等の回答では、
For Each Rangeの変数 In 指定のセル.MergeArea
このForで、結合セルを全て処理するように書いてある事が多いようです。
これでは面倒です。
以下のどれかで簡単に取得可能です。
.MergeArea.Item(1)
.MergeArea.Item(1, 1)
.MergeArea.Cells(1, 1)
いずれでも同じ結果になります。
考え方として、最も正しい指定は、Item(1)だと思われます。
上のFor Eachの最初に受け取るセルと同義になります。
下の2つは、本来は、ちょっと無理やりの指定です。
(1, 1)の行列指定が、MergeAreaを超えてしまっても取得できてしまいます。
(私は、結構使っていますが・・・)
これらについては、「第27回.RangeとCellsの深遠 」でも書いていますので、参考にして下さい。
今回は、書いてみたら、プログラムの説明があまり無かったので、急遽、結合セルについて説明をしました。
今までに何度も書いていますが、エクセルはシートの設計が第一です。
できれば、結合セルなど使用しない方が良いです。
なるべく単純化することが重要です。
作成中のエクセルでは、あまり単純化しては、ブログのネタ切れになってしまいますので(笑)
決して、このようなエクセルがお手本と言う訳ではない事だけは理解しておいて下さい。
この点は、誤解されないようにお願いします。
同じテーマ「エクセル顧客管理」の記事
第28回.納品書データをデータベース化(1)
第29回.納品書データをデータベース化(2)
第30回.配列の使い方について
第31回.売上一覧(伝票合計の一覧)を作成(1)
第32回.売上一覧(伝票合計の一覧)を作成(2)
第33回.売上一覧より納品書を作成
第34回.伝票番号の自動採番機能を追加
第35回.メニューを作成
第36回.最終回
その後№1、CSV出力を追加
その後№2、ベクター掲載
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、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)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.条件分岐(Select Case)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。