Excel2010のinputboxで複数データ一括入力について

いつもお世話になっております。アドバイスをいただきたく、お願いします。

inputboxで複数データを一括入力して帳票を連続印刷する方法についてお尋ねします。

シート「商品倉庫」：倉庫の全体図
シート「計算表」：倉庫内の各商品棚の詳細情報
シート「在庫票」：倉庫内の各棚に保管されている商品の在庫情報
棚の番号は、1から44までですが、44個連続している訳ではなく、欠番もあります。

現在のマクロは以下のとおりで、各棚の情報を「在庫票」に印刷する際、必要な商品棚番号を1個ずつ入力して1枚単位で印刷していますが、複数の棚の在庫票を一括印刷する方法はないでしょうか。
イメージとしては、
①メッセージボックスに印刷する棚番号を複数入力する（例えば1,3,6）
②メッセージボックスを連続して表示して1枚単位で印刷し、終わるときは「いいえ」を選択する。


Sub 在庫商品管理()
Worksheets("商品倉庫").Select
Dim n As Long
n = Val(InputBox("印刷する商品棚の番号を入力してください"))
If n < 1 Then Exit Sub

Select Case n
Case 1
Sheets("在庫票").Cells(5, 11).Resize(, 8).Value = Sheets("計算表").Range("B7:I7").Value
Sheets("在庫票").PrintOut copies:=1
Case 2
Sheets("在庫票").Cells(5, 11).Resize(, 8).Value = Sheets("計算表").Range("B8:I8").Value
Sheets("在庫票").PrintOut copies:=1
Case 3
Sheets("在庫票").Cells(5, 11).Resize(, 8).Value = Sheets("計算表").Range("B9:I9").Value
Sheets("在庫票").PrintOut copies:=1
：
：
：
Case 44
Sheets("在庫票").Cells(5, 11).Resize(, 8).Value = Sheets("計算表").Range("B37:I37").Value
Sheets("在庫票").PrintOut copies:=1

Case Else
　MsgBox "商品棚番号不一致です"

End Select
End Sub

質問者からの補足コメント

• サポートありがとうございます。是非、よろしくお願いします。
  
  計算表の棚情報（一覧表）は7行目から下に一番最後が37行目まで連続（31）しております。
  一方、棚番号は1からスタートして商品の分類を意識して17の次が20となるなど、1から44までのうち、欠番がいくつかあります。
  下記のコードの****の箇所に、Select Case を使わないコードを書くということでしょうか。
  
  Worksheets("商品倉庫").Select
  Dim n As Variant
  For Each n In Split(InputBox("印刷する商品棚の番号を入力してください" _
  & vbCrLf & vbCrLf & "複数の場合は番号をカンマで区切ってください"), ",")
  If n < 1 Then Exit Sub
  　　　***********
  Next n
  End Sub

  No.2の回答に寄せられた補足コメントです。 補足日時：2020/04/09 18:00

• 再度、アドバイスお願いします。是非マスターしたいです。
  
  row_def = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 49, _
  50, 51, 52, 19, 20, 21, 22, 23, 24, 25, 39, 40, 41, 42, 43, 29, 30, 31, 32, 33, 34)
  
  上記の設定では、49～52、39～43、29～34の3グループで下記のコードの設定範囲オーバーのエラーが出ました。
  Sheets("在庫票").Cells(5, 11).Resise(, 8).Value = _
  Sheets("計算表").Range("B7:I7").offset(row = def(n - 1)).Value
  
  棚番号の定義が間違っているようです。計算表の棚番号の配列は画像のとおりです。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2020/04/10 17:44

No.1 ベストアンサー 回答者： ママチャリ 回答日時： 2020/04/08 21:35

なかなかインパクトのあるソースコードですね！！

本題です。
①のイメージ（例えば1,3,6）で考えてみました。
Split 関数は、カンマ等で区切られた文字列を配列に格納してくれます。
For Each...Next ステートメントは、配列やコレクションンの要素をひとつずつ取り出しながらループしてくれます。
それを組み合わせると、こんな感じになります。
下記の MsgBox n のところをインパクトのあるコードに置き換えれば、それなりに動くと思います。

Sub sample()
Dim n As Variant
For Each n In Split(InputBox("印刷する・・・"), ",")
MsgBox n
Next n
End Sub

この回答へのお礼

ママチャリ　様
早速にありがとうございました。
InputBoxで複数番号の入力、上手く出来ました。
作業処理が楽になりました。

お礼日時：2020/04/09 18:02

No.5 回答者： fujillin 回答日時： 2020/04/11 10:11

No4です。

補足に示して頂いたコードは、No3、4で説明した内容をほぼそのまま実現なさっていると思います。

＞何故か先頭行（7行目）だけが抽出されないので、
現在、テスト環境がないので確認できませんが、Findのリファレンスを見てみると、引数Afterを省略した場合は、2番目のセルから検索されるように読み取れます。
https://docs.microsoft.com/ja-jp/office/vba/api/ …

実際にテストしていないので明言はできませんが、疑問に思われている事象は、このためではなかろうかと推測されます。
対処法としては、ご提示のようにひとつ上のセルを範囲に含めるか、Afterに最終セル（C43）を指定しておく等が考えられそうに思います。

いずれにしろ、このような方法・考え方を採用することで、数十のCase分けをして個々の処理を記述しなくても、一般化した一回分の処理で対応可能であることはご理解いただけたのではないかと思います。
勿論、どのような方法を採用するかは、プログラム作成者が自由に採択すれば良いことですが。

この回答へのお礼

何度もありがとうございました。
＞Afterに最終セル（C43）を指定しておく等が考えられそうに思います。

以下のとおり修正して上手く動きました。
Set FoundCell = .Range("C7:C43").Find(n, after:=Range("C43"))

今回は、本当にありがとうございました。今、出来上がったコードをみると、見違える内容です。
これで、棚番号の変更があった場合もCaseを使用していないのでメンテが不要になりました。

お礼日時：2020/04/11 20:25

No.4 回答者： fujillin 回答日時： 2020/04/10 23:13

No3です。

来週水曜まではエクセル環境がないので検証もできず、スマホからの回答のため、方法論と解説だけになってしまうことを、先にお断りしておきます。
どなたか別のかたが、具体的な回答を示してくださるかも知れません。

さて、No2で示した
Sheets("計算表").Range("B7:I7").offset(row = def(n - 1)).Value
の方式は、棚番号によってB7:I7からm行分シフトした行の値を参照させるという考え方で、棚番号ごとのシフト行数（m）を最初に配列で定義しておくというものです。

ご提示のコードの場合、
　row = def(n - 1)
が、定義した配列名になっていないので、存在しない配列defを参照しようとしてエラーになっていると思われます。
また、配列の定義内容も上述の内容（=シフト行数）にはなっていませんね。
配列の内容は棚番号順に定義しておく必要がありますが、逆の参照になってしまっているように見受けられます。

一方で、添付の図を見ると、No3の回答で予想したようにC列に棚番号が示されているようですので、わざわざ同じような内容を配列で定義するよりも、C列の情報を利用するNo3で示した方法の方が良さそうに感じられます。
コードのメンテナンスを考慮してもその方が良いのではないでしょうか。

No3にもかきましたが、具体的な処理としては、与えられた棚番号をC列から検索して、見つかった行番号のB列からI列までの値を使用するようにすれば良いと考えられます。
検索はワークシート関数のMATCHを利用しても出来ますし、VBAのFINDメソッドでも良いと思います。
ただし、棚番号に欠番があるとのことですので、検索してヒットしない場合も想定した処理にしておく必要があります。

ひとまずはこの程度で・・・

この回答へのお礼

ありがとうございます。FINDメソッドを調べて作ってみました。

InputBoxに入力した棚番号の行数を検索し、Offset( )に代入すると考えました。

何とか動いたのですが、１点疑問があります。
検索範囲指定で先頭行（棚番号「1」）をｃ7にすると、何故か先頭行（7行目）だけが抽出されないので、範囲をc6:ｃ43にしました。

Sub 在庫商品管理()
Dim FoundCell As Variant
Dim n As Variant
For Each n In Split(InputBox("印刷する商品棚の番号を入力してください" _
& vbCrLf & vbCrLf & "複数の場合は番号をカンマで区切ってください"), ",")
　If n < 1 Then Exit Sub

With Worksheets("計算表")
Set FoundCell = .Range("C6:C43").Find(n)
If FoundCell Is Nothing Then
MsgBox "棚番号は見つかりません。"
Else
Sheets("在庫票").Cells(5, 11).Resize(, 8).Value = _
Sheets("計算表").Range("B6:I6").Offset(FoundCell.Row - 6).Value
'Sheets("在庫票").PrintOut copies:=1
End If
End With
Next n

End Sub

お礼日時：2020/04/11 09:19

No.3 回答者： fujillin 回答日時： 2020/04/10 09:15

No2です。

＞Select Case を使わないコードを書くということでしょうか。
そういうことです。
別に、Select Caseが悪いと言っているわけではありませんので、誤解のなきように。
ただ、ご質問の場合は、Case数が多いわりに、行っている処理が行番号違いで全く同じ内容なので、もう少し要領よく記述できるのではないかと考えた次第です。

No2とは別の方法になりますが、例えば「計算表」のどこかの列に、その行が示している「商品棚番号」が記入してあるとします。（←実際にどこかにありそうな気がしますが…）
そのような情報があるなら、
　『その列から該当する商品棚を検索して、その行の値を在庫表に記述する』
という処理にまとめてしまうような考え方ができます。
（少し一般化した処理を1回分記述をすることで、全部に対応できるようになるという意味です）
あるいは、どこかに棚番号と行番号の対応表があれば、それを利用して棚番号と行番号を変換すれば同じ処理にすることができます。

No2の回答は、そのような表が存在しない場合を想定して、コード中に表に変わるものを記述して変換するような例になっています。
仮に、コード中に変換表を記述したとしても、今回の場合は、数十回のケース分けの処理を記述するのに比べれば、遥かに短い記述で済むようになることが想像できます。

実際に、どのような方式が良いのかは、コード全体の認識のしやすや、メンテナンスが発生した際の扱いやすさなどの観点から選択なさるのが宜しいのではないかと思います。
もちろん現状のままにしておいても、正しく処理できますので何ら問題はありません。

No.2 回答者： fujillin 回答日時： 2020/04/09 16:24

こんにちは

回答はすでにNo1様が記載の通りなので、こちらは回答ではありません。

ちょっと気になったのが、ご提示のコードの方です。
（もう少しなんとかならないものかと・・・）

一見すると、棚番号1が7行目、棚番号8が8行目・・・に対応しているように見えますが、棚番号44は37行目とのことなので単純な規則性があるのかないのかわかりません。
仮に棚番号44が50行目とかであるなら、各Caseの右辺は
　Sheets("計算表").Range("B6:I6").Offset(n).Value
のように統一して記述できるので、何拾通りものCaseを書き連ねる必要がなくなって、すっきりするのではないかと想像します。

上記のような規則性がなく、対応関係が飛び飛びのような場合であっても、その位置関係を別に変数に定義してしまうことで、同様の方法を用いて簡略化が可能になると考えられます。
例えば、
　row_def = Array(0, 1, 2, 3, 4, 5) '・・・実際の位置関係で飛び飛びの数値でも良い。棚番号分定義
と設定しておいて、

　Sheets("在庫票").Cells(5, 11).Resize(, 8).Value = _
　　　Sheets("計算表").Range("B7:I7").Offset(row_def(n - 1)).Value
のような感じで処理すれば、コードの記述はほぼ1ケース分だけで済みますので、およそ数十分の一のコードにまとめることが可能になりそうな気がします。