エクセルVBAで返品了解書を作成。書誌データを出版社別に処理する

syosikakoueyecatcha

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

 

今回は,返品了解書作成ソフト作りの中で、

抽出した書誌データを返品了解書のテンプレートへ転記するための準備作業の前半部分になります。

 

先回の返品了解書作成記事はこちら↓からお読みいただけます。

書店員必見!了解書作成にWebの書誌データを使う。返品の業務改善

 

返品了解書作成に関連の記事はこちらです。

「返品了解書作成」の記事一覧を開く

 

今回の返品了解書のVBA作成の進め方

 

先回の記事では、「書誌データ貼付」シートとその動作させるVBAを作成しました。

そして抽出した書誌データを「抽出データ」シートに送りました。

 

今回はその送られた書誌データを出版社毎に仕分けする作業を行います。

 

①「出版社リスト」シートに返品了解を依頼する出版社名を取り出します。

②その依頼する出版社毎に出版社名のSheetを新しく作成します。

③出版社名のシートにそれぞれの返品書誌データを仕分けしていきます。

 

エクセルVBAで「抽出データ」シートの組み立て

 

この作業用に新たな標準モジュールを挿入追加します。

 

(挿入の方法はこちら↓で確認できます。)
VBE(ビジュアルベーシックエディター)を起動する

参考記事

エクセルVBA 始めての起動。VBEの立ち上げ、保存と終了

 

「出版社リスト」シートへ出版社名を記述します

 

「抽出データ」シートに取り出した書誌データには、同じ出版社で違う書名が混在していることもあると思います。

「出版社」シートには、どれだけの種類の出版社がデータとして挙がっているかをリストアップします。

リストアップにはVBAの”Dictionaryオブジェクト”を使って重複チェックを行います。

 

準備作業として「参照設定」からランタイムの参照が出来るようにします。

参照設定で「Microsoft Scripting Runtime」を導入

「ツール」から「参照設定…」をクリックします。

syosisyori001a

 

「参照設定ーVBAProject」ウインドウで、「Microsoft Scripting Runtime」を選択し「OK」をクリックしてください。

優先順位「上矢印」を繰って上位に上げておきましょう。

syosisyori002a

 

Subプロシージャ名は「出版社名リストアップ」としてください。


Sub 出版社名リストアップ()
End Sub

 

 

MEMO

重複データの整理についてはこちらの記事を参考にしてください。

エクセルVBA 同じ項目をまとめる(重複データの整理)コード作成

 

変数の宣言

 

「Dictionaryオブジェクト」を宣言します。

「Dictionaryオブジェクト」・・・・・myDic

「出版社名」・・・・・SyuN

コード化するとこのようになります。

'変数宣言
Dim s As Long
Dim t As Long
Dim tyuRow As Long
Dim SyuN As Variant
Dim myDic As Dictionary

Set myDic = New Dictionary

 

出版社リストシートへ出版社名を送る

 

重複チェックをしながら出版社名データを送るコードは

このようになります。


'出版社名を送る
Worksheets("抽出データ").Select
  Aho = Worksheets("抽出データ").Range("C2").Value
  tyuRow = Cells(Rows.Count, 2).End(xlUp).Row
  For s = 1 To tyuRow
    SyuN = Cells(s, 3).Value
    If myDic.Exists(SyuN) = False Then
      myDic.Add SyuN, ""
    End If
  Next s

Worksheets("出版社リスト").Select
  For t = 1 To myDic.Count - 1
    Cells(t, 1).Value = myDic.Keys(t)
  Next t

End Sub

 

出版社別に新しくSheetを作成します

 

新たにSubプロシージャ名を「出版社シート」として追加します。

 

MEMO

入力セルの最終行を取得する方法はこちらを参考にしてください。

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

 

Sub 出版社シート()

Dim SyuRow As Long
Dim SN As Variant
Dim t As Long

Worksheets("出版社リスト").Select
  SyuRow = Cells(Rows.Count, 1).End(xlUp).Row
  For t = 1 To SyuRow
    SN = Range("A" & t).Value
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = SN
  Worksheets("出版社リスト").Select
  Next t

End Sub

シート名を付ける場合、

先に、必要な数のシートを作ってから後で一つづつ名前付けをするよりも

新たなシートを作るときに、同時に名前を付けて行くというのが一番簡単な方法になります。

 

出版社別シートに書誌データを振り分けます

 

「出版社リスト」から一つづつ出版社名を拾って「抽出リスト」から該当データを抜き取り、

それぞれの「出版社シート」に転記していきます。

Subプロシージャ名を「出版社振り分け」として追加します。


Sub 出版社振り分け()

Dim SyuRow As Long
Dim SN As Variant
Dim s As Long
Dim t As Long
Dim n As Long

Worksheets("出版社リスト").Select
  SyuRow = Cells(Rows.Count, 1).End(xlUp).Row
  For t = 1 To SyuRow
    SN = Range("A" & t).Value
    Worksheets("抽出データ").Select
    tyuRow = Cells(Rows.Count, 3).End(xlUp).Row
    s = 1
    For n = 1 To tyuRow
      If Range("C" & n).Value = SN Then
        Rows(n).Copy Destination:=Worksheets(SN).Range("A" & s)
        s = s + 1
        Worksheets(SN).Columns("A:G").AutoFit
      End If
      Worksheets("抽出データ").Select
    Next n
    Worksheets("出版社リスト").Select
  Next t

End Sub

 

「返品書誌データ整理」ボタンの設置

 

「操作ボタン」シートに「返品書誌データ整理」プロシージャーを起動するボタンを設置します。

エラー処理として、対策のためのコードを埋め込みます。
「抽出データ」が空の時(返品書誌データが抽出されていないとき)はプロシージャーを終了します。

今までに作成したSubプロシージャを連続で順番に起動するためのSubプロシージャとして、

Sub「返品書誌データ整理」プロシージャーを作成します。

Sub 返品書誌データ整理()
Worksheets("抽出データ").Select
  R = Cells(Rows.Count, 2).End(xlUp).Row
  If R <= 1 Then
      MsgBox "返品データが取り出されていません!"
      Worksheets("操作ボタン").Select
      Range("A1").Select
      Exit Sub
  End If
  出版社名リストアップ
  出版社シート
  出版社振り分け

End Sub

「操作ボタン」シートでの「返品書誌データ整理」ボタンの設置場所は特に指定はありません。

ボタンの設置方法についてはこちらでも↓確認できます。

データ抽出ボタンを設置する。

VBAコード参考記事
ここの説明で出てくるVBAコードの参考にしていただける記事です。

vbacalleyecatch Callステートメントはプロシージャーの部品化に必須項目 エクセルVBA最速理解
MEMO

コマンドボタンの設置方法はこちらの記事も参考になります。

エクセルVBA コマンドボタンをシートに簡単設置する方法

 

syosisyori003a

 

今回作成したVBAコード

 

syosisyori005a

抽出データシートからの書誌データ整理まとめ

 

今回はランダムに行われた「抽出データ」シートのデータを整理整頓して、

出版社名毎のシートに仕分けするところまで行いました。

 

次回は、エクセルBOOK「返品了解申請.xlsm」へデータを転送するVBA作成となります。

 

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

 

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

 

改訂新版 てっとり早く確実にマスターできるExcel VBAの教科書
定番参考書の改定新版が、動画付きになりもっと分かり易くなった
vbastudy022a
vbastudy023a

電子書籍版「改訂新版 てっとり早く確実にマスターできるExcel VBAの教科書」をamazonで見てみる

(著者)大村あつし
(出版社)技術評論社
(税込価格)2,508円(本体2,280円+税)

30冊を超えるExcelのマクロやVBAの解説書を執筆してきた著者による考え抜かれた本書の内容と構成。
独創的な解説手法で必ずExcel VBAが理解できます!
初級からの参考書ですが、より実践的切り口での解説をしています。
QRコードから操作の流れを動画(無音です)で確認することもできるようになりました。
文章解説と動画との関係性は、主は文章での解説、サポートが動画になります。

 

次の記事を読むのはこちら↓から

返品了解書作成のVBA。出版社リストのデータを他のエクセルに送る

 

エクセルVBAを使って、業務効率UPを図る上で始めのうちに知っておきたい内容を纏めています。

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

 

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

 

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