[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『棚表一覧から使用していない棚番だけコンボボックスに表示』（ひろ）
現在、フリーロケーションで入庫が可能な倉庫管理を作成しています。
入庫用フォーム上で、使われていない棚番だけをコンボボックスで
選択できるようにしたいのですがうまく表示されません。
現在のシート構成は以下の通りです。
シート1(シート名：在庫) A B C D E 在庫ID 商品CD 棚番 賞味期限 在庫数 I0005 A00001 A-01-1-01 2015/12/31 5 I0006 A00002 A-01-1-02 2016/1/1 6
シート2(シート名：棚番マスタ) A B C 棚番CD 倉庫CD 棚番 T00001 W00001 A-01-1-01 T00002 W00001 A-01-1-02 T00003 W00001 A-01-1-03 T00004 W00001 A-01-1-04 T00005 W00001 A-01-1-05 T00006 W00002 B-01-1-01 T00007 W00002 B-01-1-02 T00008 W00002 B-01-1-03 T00009 W00002 B-01-1-04 T00010 W00002 B-01-1-05
シート3(シート名：倉庫マスタ) A B C 倉庫CD 倉庫名 記号 W00001 倉庫A A W00002 倉庫B B
入庫フォーム 倉庫名：コンボボックス 棚番：コンボボックス (その他、商品CD、数量等を入力するテキストボックスなどありますが、 今回、あまり関係ないと思われますので省かせて頂きます。)
入庫フォームで棚番を選択する際、倉庫名を選択してその倉庫に該当する 棚番からシート1のC列(棚番)で使用している棚番を除いたものだけを シート2のC列(棚番)から抽出してコンボボックスに表示したい。
出来るだけ作業列(作業シート)等を使わないで、VBA内部で処理を完結させたいと
考えております。
以上、ご教授お願いします。
＜ 使用 Excel：Excel2010、使用 OS：WindowsXP ＞
回答じゃないんですが、在庫シートはどっかで計算された「出力」結果ですか？ 履歴はどこか別のところに保管されていて、常に在庫があるものだけ出力されるシートなんですかね？
要は １）在庫0のものは在庫シートに残るのか、 残した場合・・・ ２）また入庫の可能性があるので残してあり、除く対象なのか ３）それとも0は空いているものとして除かない対象なのか
が曖昧ではないかと思います。 また定時されたデータの場合、どのようにコンボボックスの値が入っていればよいのかも 併せて記述頂けば、より回答し易くなるのではないでしょうか？ （稲葉） 2015/12/15(火) 09:34
稲葉さん
説明不足大変申し訳ございません。
ご質問いただいた内容について以下の通り回答します。
在庫シートは、入力フォーム上で入力した内容です。 入庫履歴は別シート(入庫)に保管されており、棚の在庫がある間だけ 在庫シートに記録されている状態となります。 棚が在庫0になった時、在庫シートから削除されます。 こちらの処理は出庫時に処理したいと考えています。
棚番コンボボックスへは棚番シートC列の棚番が表示され選択できるように したいと考えています。
以上、宜しくお願いします。
（ひろ） 2015/12/15(火) 09:51
サンプルです。 よりよい方法はあると思いますが、一例程度に考えてください。 倉庫名はB列の値が表示され、使用する値はB列の「倉庫CD」です。 棚番はC列しか取り込んでおりませんので、A/B列の値はComboBoxから取得できません。
ComboBox1・・・倉庫名 ComboBox2・・・棚番
１）新しいフォームにComboBoxを二つ配置してください ２）フォームに以下のコードを張り付けてください
Option Explicit
Dim SOKO As Worksheet
Dim ZAIK As Worksheet
Dim TANA As Worksheet
Private Sub ComboBox1_Change()
Dim w
Dim n As Long
Dim i As Long
With TANA
n = .Cells(Rows.Count, "A").End(xlUp).Row
'配列関数を使い、1次元の配列を取得する
'IF(B1:B5="倉庫A",C1:C5,CHAR(2)) ←B1が倉庫Aなら、C1、そうじゃなければコード(2)の文字列を入れる
'TRANSPOSEでw(1,1)の配列をw(1)の配列に変換
w = Evaluate("TRANSPOSE(IF(" & .Name & "!B2:B" & n & "=""" & ComboBox1.Value & """," & .Name & "!C2:C" & n & ",CHAR(2)))")
'Filter関数で、Chr(2)を除去する
'CHARはワークシート関数で、ChrがVBAの関数
w = Filter(w, Chr(2), False)
End With
If UBound(w) > 0 Then
With ZAIK
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
'Chr(2)を除去したやり方と同じで、在庫表のC列の値を除去する
w = Filter(w, .Cells(i, "C").Value, False)
Next i
End With
End If
ComboBox2.List = w
End Sub
Private Sub UserForm_Initialize()
Set SOKO = Sheets("倉庫マスタ")
Set ZAIK = Sheets("在庫")
Set TANA = Sheets("棚番マスタ")
With ComboBox1
.List = SOKO.Range("C2", SOKO.Cells(Rows.Count, "A").End(xlUp)).Value
.ColumnCount = 2 '2列表示に設定
.ColumnWidths = "0;20" '1列目を0pt、2列目を20ptに設定
.BoundColumn = 1 '実際に使用する値の列番号（A列の「倉庫CD」）
End With
End Sub
1152補足追加 （稲葉） 2015/12/15(火) 11:31
稲葉さん
早々のご回答ありがとうございます。 早速提示頂きましたサンプルの内容を確認し、 動作確認させて頂きます。
結果については後ほどご報告いたします。 （ひろ） 2015/12/15(火) 11:38
稲葉さん
補足の追記ありがとうございます。 問題なく動作していること確認できました。
> w = Evaluate("TRANSPOSE(IF(" & .Name & "!B2:B" & n & "=""" & ComboBox1.Value & """," & .Name & "!C2:C" & n & ",CHAR(2)))")
"Evaluate"こんな素晴らしいメソッドがあったんですね。 これまで自分で考え、何行にも渡ってコードを記述していた部分が たった一行に収まっており感動しています。
>w = Filter(w, .Cells(i, "C").Value, False)
これまでずっと、在庫シートにないものを一行ずつ配列に追加していく方向で考えていました。 全く逆の発想、今後参考にさせていただきます。
今回と同様の事案は今後もあると思いますので、今回教えて頂いたコードの理解を深め 今後も応用させて頂きます。
以上、ご対応ありがとうございました。
（ひろ） 2015/12/15(火) 14:02
＞ 今回と同様の事案は今後もあると思いますので、今回教えて頂いたコードの理解を深め とのことなんで、こちらに詳しく事例があります [[20070212134849]] 『Evaluate Method の研究』（seiya） >>BOT （稲葉） 2015/12/15(火) 15:08
稲葉さん
参考スレを教えて頂きありがとございます。
"Evaluate"にはいろいろな使い方があるんですね。 すごく参考になりました。
（ひろ） 2015/12/15(火) 15:58
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.