いつもお世話になっております。アドバイスをいただきたく、お願いします。
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
No.1ベストアンサー
- 回答日時:
なかなかインパクトのあるソースコードですね!!
本題です。
①のイメージ(例えば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で複数番号の入力、上手く出来ました。
作業処理が楽になりました。
No.5
- 回答日時:
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を使用していないのでメンテが不要になりました。
No.4
- 回答日時:
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点疑問があります。
検索範囲指定で先頭行(棚番号「1」)をc7にすると、何故か先頭行(7行目)だけが抽出されないので、範囲をc6:c43にしました。
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
No.3
- 回答日時:
No2です。
>Select Case を使わないコードを書くということでしょうか。
そういうことです。
別に、Select Caseが悪いと言っているわけではありませんので、誤解のなきように。
ただ、ご質問の場合は、Case数が多いわりに、行っている処理が行番号違いで全く同じ内容なので、もう少し要領よく記述できるのではないかと考えた次第です。
No2とは別の方法になりますが、例えば「計算表」のどこかの列に、その行が示している「商品棚番号」が記入してあるとします。(←実際にどこかにありそうな気がしますが…)
そのような情報があるなら、
『その列から該当する商品棚を検索して、その行の値を在庫表に記述する』
という処理にまとめてしまうような考え方ができます。
(少し一般化した処理を1回分記述をすることで、全部に対応できるようになるという意味です)
あるいは、どこかに棚番号と行番号の対応表があれば、それを利用して棚番号と行番号を変換すれば同じ処理にすることができます。
No2の回答は、そのような表が存在しない場合を想定して、コード中に表に変わるものを記述して変換するような例になっています。
仮に、コード中に変換表を記述したとしても、今回の場合は、数十回のケース分けの処理を記述するのに比べれば、遥かに短い記述で済むようになることが想像できます。
実際に、どのような方式が良いのかは、コード全体の認識のしやすや、メンテナンスが発生した際の扱いやすさなどの観点から選択なさるのが宜しいのではないかと思います。
もちろん現状のままにしておいても、正しく処理できますので何ら問題はありません。
No.2
- 回答日時:
こんにちは
回答はすでに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ケース分だけで済みますので、およそ数十分の一のコードにまとめることが可能になりそうな気がします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) マクロで最終行から上に検索を逆にしたい 1 2022/05/17 18:27
- Visual Basic(VBA) VBA Userformで一部別シートに転記がしたいのですが 2 2023/05/24 13:08
- Visual Basic(VBA) VBAでのループ順序について 3 2023/03/13 10:55
- Excel(エクセル) エクセルの印刷マクロについて質問があります。 現在、下記のマクロで印刷しています。Sheet1のD6 5 2023/06/12 10:59
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Excel(エクセル) 並べ替え、ソートの構文がわからない。 お世話になります。VBA超初心者です。 エクセルでワークシート 2 2023/06/28 21:00
- Visual Basic(VBA) ExcelのVBAコードについて教えてください。 4 2023/05/26 10:43
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) VBA 請求書自動作成 3 2022/04/24 01:58
- Excel(エクセル) vba userformで漢字を全角カタカナに 2 2022/07/24 15:38
このQ&Aを見た人はこんなQ&Aも見ています
-
10代と話して驚いたこと
先日10代の知り合いと話した際、フロッピーディスクの実物を見たことがない、と言われて驚きました。今後もこういうことが増えてくるのかと思うと不思議な気持ちです。
-
人生最悪の忘れ物
今までの人生での「最悪の忘れ物」を教えてください。 私の「最悪の忘れ物」は「財布」です。
-
これが怖いの自分だけ?というものありますか?
人によって怖いもの(恐怖症)ありませんか? 怖いものには、怖くなったきっかけやエピソードがあって聞いてみるとそんな感覚もあるのかと新しい発見があって面白いです。
-
この人頭いいなと思ったエピソード
一緒にいたときに「この人頭いいな」と思ったエピソードを教えてください
-
我がまちの「給食」自慢を聞かせてっ!
富山県の給食には「ベニズワイガニ」が出る、、、なんて話を聞いたことがあります。 日本全国「え、給食にそれ出るの!?」な驚きメニューがまだまだあるはず!
-
複数行の入力を受け取る方法
Visual Basic(VBA)
-
Excel-VBAでInputBox+Pulldownは可能??
Visual Basic(VBA)
-
エクセルのラベルの値(文字列)を垂直方向で中央揃えにするには?
Excel(エクセル)
-
-
4
VBAでInputBoxの再入力をさせるには?
Visual Basic(VBA)
-
5
メッセージボックスに表示する文字を大きくしたい
Excel(エクセル)
-
6
もしセルが#N/A"なら~をする・・・には?"
Excel(エクセル)
-
7
VBAのコマンドボタンの文字列の改行方法は?
Visual Basic(VBA)
-
8
VBAで文字列を数値に変換したい
Excel(エクセル)
-
9
VBA ユーザーフォームのChangeイベントを停止したい
Access(アクセス)
-
10
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
11
フォントの大きさ
Visual Basic(VBA)
-
12
Access VBAで行ラベルが定義されていないというエラーが出ます
Access(アクセス)
-
13
DATE型変数を初期化する方法
Visual Basic(VBA)
-
14
VBAでエクセルシートを更新(リフレッシュ)する方法を教えて下さい。
Excel(エクセル)
-
15
InputBoxの入力値を半角数字のみと限定する方法
Excel(エクセル)
-
16
VBAでダブルコーテーション入りの数式をセルにセットしたい
Visual Basic(VBA)
-
17
InputBox内の表示について
PowerPoint(パワーポイント)
-
18
EXCEL VBA でインプットボックスを開いたときの入力モードの設定について
Excel(エクセル)
-
19
「Columns(A:C")」の列文字を数字にして表記したい"
Excel(エクセル)
-
20
ExcelのVBA。public変数の値が消える
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・「みんな教えて! 選手権!!」開催のお知らせ
- ・漫画をレンタルでお得に読める!
- ・【大喜利】【投稿~12/6】 西暦2100年、小学生のなりたい職業ランキング
- ・ちょっと先の未来クイズ第5問
- ・これが怖いの自分だけ?というものありますか?
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・2024年のうちにやっておきたいこと、ここで宣言しませんか?
- ・とっておきの「夜食」教えて下さい
- ・これまでで一番「情けなかったとき」はいつですか?
- ・プリン+醤油=ウニみたいな組み合わせメニューを教えて!
- ・タイムマシーンがあったら、過去と未来どちらに行く?
- ・遅刻の「言い訳」選手権
- ・好きな和訳タイトルを教えてください
- ・うちのカレーにはこれが入ってる!って食材ありますか?
- ・おすすめのモーニング・朝食メニューを教えて!
- ・「覚え間違い」を教えてください!
- ・とっておきの手土産を教えて
- ・「平成」を感じるもの
- ・秘密基地、どこに作った?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・10代と話して驚いたこと
- ・大人になっても苦手な食べ物、ありますか?
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
- ・都道府県穴埋めゲーム
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBA 変数名に変数を使用したい。
-
配列の中の最大値とそのインデ...
-
vba フィルター 複数条件 3つ以...
-
エクセルでXY座標に並べられた...
-
OutOfMemoryExceptionの回避策...
-
コンボボックスのインデックス...
-
配列のペースト出力結果の書式...
-
VCのDLL内でmallocした構造体を...
-
Dir関数で読み取り順を操作でき...
-
定数配列の書き方
-
C#でbyte配列から画像を表示さ...
-
Excelのメモリ(配列)の上限は2G...
-
複数のtextboxの処理を一括で行...
-
エクセルの列一括書き込み
-
構造体配列の特定のメンバーをF...
-
VB.NETでファイルを後ろから読...
-
1次元配列をワークシートに高...
-
2次元配列の初期値
-
VB.netでの動的配列の扱い
-
大量の変数を定義するにはどう...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBA 変数名に変数を使用したい。
-
C#でbyte配列から画像を表示さ...
-
エクセルでXY座標に並べられた...
-
Excel2010のinputboxで複数デー...
-
vba フィルター 複数条件 3つ以...
-
Dir関数で読み取り順を操作でき...
-
配列の中の最大値とそのインデ...
-
構造体配列の特定のメンバーをF...
-
配列のペースト出力結果の書式...
-
VB6のメモリ解放に関して
-
COBOLの基本的な事なので...
-
定数配列の書き方
-
Excelのメモリ(配列)の上限は2G...
-
OutOfMemoryExceptionの回避策...
-
Segmentation Fault (メモリ制限?)
-
Redim とEraseの違いは?
-
VBAでMODE関数をつくる
-
VB.NETの配列にExcelから読み込...
-
CheckBoxの配列化
-
コンボボックスのインデックス...
おすすめ情報
サポートありがとうございます。是非、よろしくお願いします。
計算表の棚情報(一覧表)は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
再度、アドバイスお願いします。是非マスターしたいです。
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
棚番号の定義が間違っているようです。計算表の棚番号の配列は画像のとおりです。