みなさん、こんにちはケンケンです。
エクセルを駆使して販売管理システムを作っています。
どの程度のシステムが完成するかまだ分かりませんが、ひとつずつ関門を突破して少しでも精度の高いものを作っていきたいと思いますのでお付き合いください。
前回は、見積書を自動作成する基本をご紹介しました。内容としては、見積書を１枚作成することができました。
今回は、見積書をデータがある限りすべてに対応して作成する方法をご紹介します。
少しのコツで自動化を実現できるのでご期待ください。
前回の復習
前回書いたコードが以下になります。
Sub data_tenki()
Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value
ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value
ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value
ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value
ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value
ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A2").Value
ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B2").Value
ActiveSheet.Range("B16:G18").Value = Sheets("見積データ一覧").Range("D2:I4").Value
ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L2").Value
Sheets("得意先貼付元").Range("B1").Value = "〒" & Sheets("得意先一覧").Range("C2").Value
Sheets("得意先貼付元").Range("B2").Value = Sheets("得意先一覧").Range("D2").Value
Sheets("得意先貼付元").Range("B3").Value = Sheets("得意先一覧").Range("E2").Value
Sheets("得意先貼付元").Range("B4").Value = Sheets("得意先一覧").Range("B2").Value & " 御中"
Sheets("得意先貼付元").Range("B1:B4").Copy
ActiveSheet.Range("B2").Select
ActiveSheet.Pictures.Paste.Select
End Sub
見積書のテンプレートをコピーし、そのシートに各データを転記しました。
さらに図の貼り付けもしましたね。
これらのコードは自動化させる準備として大変重要なのでしっかり習得しておきましょう。
今回は、上記コードを修正することで自動化を実現させます。
見積書は何枚作る必要があるだろうか？
自動化を考える第一歩として「何を何回繰り返せばいいか」を考えるといいでしょう。
その疑問から得られる答えをコードで表現すれば自動化できるはずですね。
今回の命題に照らし合わせると「見積書作成を何回繰り返すか」を考えればいいですね。
If文でデータを比較する方法
見積データが格納されている「見積データ一覧」シートから繰り返すべき回数を計算してみましょう。
A列の見積Noを見ると良さそうです。
上図を見ると答えは３回ですね。
それでは、この３回という答えを出す方法を考えてみましょう。
いろいろあるかと思いますが、
A列のデータを上から見ていってデータの境目の数を数えればよさそうです。
ということは、見積Noの境目を把握できれば良いですね。上の行と下の行を比較して違う内容のデータが入っていたら境目である、と認識します。
それをコードで表現してみます。前回のコードに修正を加えます。
Sub data_tenki()
If Sheets("見積データ一覧").Range("A2").Value <> Sheets("見積データ一覧").Range("A3").Value Then '①
Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value
ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value
ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value
ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value
ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value
ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A2").Value
ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B2").Value
ActiveSheet.Range("B16:G18").Value = Sheets("見積データ一覧").Range("D2:I4").Value
ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L2").Value
Sheets("得意先貼付元").Range("B1").Value = "〒" & Sheets("得意先一覧").Range("C2").Value
Sheets("得意先貼付元").Range("B2").Value = Sheets("得意先一覧").Range("D2").Value
Sheets("得意先貼付元").Range("B3").Value = Sheets("得意先一覧").Range("E2").Value
Sheets("得意先貼付元").Range("B4").Value = Sheets("得意先一覧").Range("B2").Value & " 御中"
Sheets("得意先貼付元").Range("B1:B4").Copy
ActiveSheet.Range("B2").Select
ActiveSheet.Pictures.Paste.Select
End If '①
End Sub
①条件分岐を使ってA列の行を上下で比較して、データに相違があったらIf以下を実行します。
EndIfまでしっかり書くことを忘れずに。
ちなみにこのコードを実行しても何も起こりません。なぜなら、比較している対象がA2セルとA3セルで双方同じなので条件を満たしませんね。
試しにA4セルとA5セルを比較して実行してみると条件を満たすのでIf以下の処理が実行されます。
条件分岐を繰り返そう
上記のように条件分岐ができたら今度はそれを繰り返します。
ForNextを使います。
Sub data_tenki()
Dim gyoA
For gyoA = 2 To 7 '②
If Sheets("見積データ一覧").Range("A" & gyoA).Value <> Sheets("見積データ一覧").Range("A" & gyoA + 1).Value Then '③
Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value
ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value
ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value
ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value
ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value
ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A2").Value
ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B2").Value
ActiveSheet.Range("B16:G18").Value = Sheets("見積データ一覧").Range("D2:I4").Value
ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L2").Value
Sheets("得意先貼付元").Range("B1").Value = "〒" & Sheets("得意先一覧").Range("C2").Value
Sheets("得意先貼付元").Range("B2").Value = Sheets("得意先一覧").Range("D2").Value
Sheets("得意先貼付元").Range("B3").Value = Sheets("得意先一覧").Range("E2").Value
Sheets("得意先貼付元").Range("B4").Value = Sheets("得意先一覧").Range("B2").Value & " 御中"
Sheets("得意先貼付元").Range("B1:B4").Copy
ActiveSheet.Range("B2").Select
ActiveSheet.Pictures.Paste.Select
End If
Next '②
End Sub
②gyoAという変数を宣言し、２～７行目で繰り返します。繰り返し処理の最後にNextを書くのを忘れないようにしましょう。
③比較する行数を変数gyoAを使って表現します。
上と下の行をgyoAとgyoA+1とすることで表現しています。右辺と左辺を比較して値が相違した場合に以下のコード処理を実行します。これで常に変数の範囲内で上下比較することができるはずです。
それではコードを実行しましょう。
３枚作成されていれば成功です。
最初に確認した回数分だけ繰り返すことができました。
転記データを工夫しよう
欲しい分だけ見積書を作ることはできましたが、中身は正しくないですね。
転記されたデータは常に同じになってしまっているので、ここを工夫して個別のデータを転記できるようコードを修正しましょう。
まずは、「見積データ一覧」シートから転記するデータを正しくしていきます。
以下のようにコードを修正します。
Sub data_tenki()
Dim gyoA
Dim gyo '④
gyo = 2 '④
For gyoA = 2 To 7
If Sheets("見積データ一覧").Range("A" & gyoA).Value <> Sheets("見積データ一覧").Range("A" & gyoA + 1).Value Then
Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value
ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value
ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value
ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value
ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value
ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A" & gyo).Value '⑤⑧
ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B" & gyo).Value '⑤⑧
ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L" & gyo).Value '⑤⑧
ActiveSheet.Range("B16:G" & gyoA - gyo + 16).Value = Sheets("見積データ一覧").Range("D" & gyo & ":I" & gyoA).Value '⑥
Sheets("得意先貼付元").Range("B1").Value = "〒" & Sheets("得意先一覧").Range("C2").Value
Sheets("得意先貼付元").Range("B2").Value = Sheets("得意先一覧").Range("D2").Value
Sheets("得意先貼付元").Range("B3").Value = Sheets("得意先一覧").Range("E2").Value
Sheets("得意先貼付元").Range("B4").Value = Sheets("得意先一覧").Range("B2").Value & " 御中"
Sheets("得意先貼付元").Range("B1:B4").Copy
ActiveSheet.Range("B2").Select
ActiveSheet.Pictures.Paste.Select
gyo = gyoA + 1 '⑦
End If
Next
End Su
④新たに変数gyoを宣言し、初期値として２を格納しておきます。
この数字は見積データ一覧シートの最初の行を表しています。と同時に見積書を作る際に必要な最初の行を意味しています。
⑤見積データ一覧シートから見積書に転記するデータを変数gyoを使って表現しています。
⑥変数gyoとgyoAを使って転記元と転記先のセルアドレスを表現しています。
Rangeオブジェクトでセルアドレスを指定する際に、変数を当て込む方法は以前に紹介していますが、ここでもまた復習しておきましょう。
２つの変数に具体的に数字を代入してどのようにアクションするか確認しておいてください。
⑦変数gyoは初期値の２が与えられていますが、見積書を一枚作ったら数値を変更しなければなりませんね。
初期値の２のままだと転記されるデータが常に２行目スタートになってしまいます。
次の見積書を作る際には変数gyoの値を変更しておく必要があります。
それでは、どの値が適当でしょうか。
具体的に見てみると、見積書Noの境目にプラス１した値を格納しておくと次の見積書の先頭を表現できます。
コードで表現するとgyoA+1となります。
この値を変数gyoに再度格納して次の見積書作成に進みます。
⑧前述のとおり二回目以降の繰り返しでは変数gyoは変化しています。
正確に言うと見積書を作成すべきデータの始めの行が格納されています。
以下データがなくなるまで変数gyoに格納される数値は変化します。
コードを実行すると見積データが正しく転記されることを確認して下さい。
得意先一覧部分を工夫しよう
残念ながらまだ完成していません。
どこが完成していないかと言うと左上の先方会社名が正しく反映していないのです。
これも見積データを転記するときに使った、上下の行を比較する方法で検索し転記しましょう。
Sub data_tenki()
Dim gyoA
Dim gyo
Dim tokuisakiGyo '⑨
gyo = 2
For gyoA = 2 To 7
If Sheets("見積データ一覧").Range("A" & gyoA).Value <> Sheets("見積データ一覧").Range("A" & gyoA + 1).Value Then
Sheets("見積書(元)").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("F6").Value = Sheets("会社情報").Range("A2").Value
ActiveSheet.Range("F7").Value = Sheets("会社情報").Range("B2").Value
ActiveSheet.Range("G7").Value = Sheets("会社情報").Range("C2").Value
ActiveSheet.Range("F8").Value = Sheets("会社情報").Range("D2").Value
ActiveSheet.Range("F9").Value = "TEL " & Sheets("会社情報").Range("E2").Value & " :FAX " & Sheets("会社情報").Range("F2").Value
ActiveSheet.Range("H3").Value = Sheets("見積データ一覧").Range("A" & gyo).Value
ActiveSheet.Range("H4").Value = Sheets("見積データ一覧").Range("B" & gyo).Value
ActiveSheet.Range("G38").Value = Sheets("見積データ一覧").Range("L" & gyo).Value
ActiveSheet.Range("B16:G" & gyoA - gyo + 16).Value = Sheets("見積データ一覧").Range("D" & gyo & ":I" & gyoA).Value
For tokuisakiGyo = 2 To 7 '⑨
If Sheets("得意先一覧").Range("B" & tokuisakiGyo).Value = Sheets("見積データ一覧").Range("C" & gyo).Value Then '⑩
Sheets("得意先貼付元").Range("B1").Value = "〒" & Sheets("得意先一覧").Range("C" & tokuisakiGyo).Value '⑪
Sheets("得意先貼付元").Range("B2").Value = Sheets("得意先一覧").Range("D" & tokuisakiGyo).Value '⑪
Sheets("得意先貼付元").Range("B3").Value = Sheets("得意先一覧").Range("E" & tokuisakiGyo).Value '⑪
Sheets("得意先貼付元").Range("B4").Value = Sheets("得意先一覧").Range("B" & tokuisakiGyo).Value & " 御中" '⑪
Sheets("得意先貼付元").Range("B1:B4").Copy
ActiveSheet.Range("B2").Select
ActiveSheet.Pictures.Paste.Select
End If
Next
gyo = gyoA + 1
End If
Next
End Sub
上記コードを上書きしてください。
⑨変数tokuisakiGyoを用意します。この変数は得意先一覧シートから得意先名を検索するために使います。
下図をイメージすると分かり易いかと思います。
⑩この変数を使い得意先一覧と見積データが合致した場合にIf文以下を実行します。
比較対象は、
見積データで取得した得意先名→「Sheets(“見積データ一覧”).Range(“C” & gyo).Value」と
得意先一覧に記載されている得意先名→「Sheets(“得意先一覧”).Range(“B” & tokuisakiGyo).Value」です。
⑪上記でデータが合致したらいったん得意先貼付元シートに転記し、そのデータをコピーして見積書に図として貼り付けます。
図の貼り付けに関しては、前回とコードは変わりありません。
さて、ここまでできたら実行してみましょう。
すべての見積書の左上の得意先名が正しく表示されていればOKです。
これで見積データ一覧に記載されたデータの通りに見積書を自動作成することが出来ました。
まとめ
いかがだったでしょうか。
今回は、作った資料を繰り返し処理を使って自動化する方法をご紹介しました。
以前にも同様の方法をご紹介しましたが、今回は復習の意味も込めてご紹介しました。
少し違った角度からチャレンジすると応用することが容易になるので、何度でも復習して自分のものにしてください。
次回は、見積書を期間指定して作成する方法にチャレンジしてみたいと思いますのでお楽しみに。
それでは、また次回お会いしましょう。
販売管理システム作成記事一覧～見積編～
エクセルを駆使して販売管理システムを作ってみる【元データの取り扱い方】
【販売管理システム作成】見積書を期間指定して作成する方法①【コードのカスタマイズ】
【販売管理システム作成】見積書を期間指定して作成する方法②【リストボックス作成】
【販売管理システム作成】見積書を期間指定して作成する方法③【リストボックス作成その２】
【販売管理システム作成】見積データ登録用のフォームを作ろう①【フォームの設定】
【販売管理システム作成】見積データ登録用のフォームを作ろう②【コンボボックスの活用】
【販売管理システム作成】見積データ登録用のフォームを作ろう③【リストボックスの活用】
【販売管理システム作成】見積データ登録用のフォームを作ろう④【エクセルシートに登録】
【販売管理システム作成】見積データ登録用のフォームを作ろう⑤【修正削除機能の実装】
【販売管理システム作成】登録済みのデータを修正削除する方法【見積システム完成編】
