[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ユーザーフォームで検索』（ひなの）
お世話になっています。
[[20160701213502]] 『2-UserForm、ListBoxに指定の値の該当値を別のLis』
前回いろいろと教えていただいてありがとうございます。
あれから実用化したなかで、いくつか変更しないとちょっと大変な部分がでてきで、また質問に参りました。今回も教えていただけると嬉しいです。
変更の部分は↓この部分です。
Q2.ListBox1の値をクリック後ListBox2に売上の値を表示 ・売上表示条件（同じ商品NO・同じ金額の場合＝一行にする 同じ商品NO・違う金額の場合＝そのまま表示する）
↑これを↓
Q.請求書B4のセルと商品A列に検索かけ 客NOある時は該当する客NOの列をlistbox2 に表示に変更したいです。 客NOない時は商品K列をlistbox2 に表示に変更したいです。
'該当する客NOの列を検索
With Sheets("商品")
Ap = Application.Match(Worksheets("請求書")Range("B4").Value, Worksheets("商品").Range("A:A"), 0)
'データ存在チェックない時のみ転記する
'該当の列を ListBox2 に表示
' ListBox2 の表示内容はB列商品NO C列商品名 AP列空欄以外の値
'空白以外のセル, 範囲はまだわからないので、省いてあります。
ActiveSheet.AutoFilter Field:=Ap, Criteria1:="<>"
Q1.ユーザーフォームであいまい検索
TextBox1に「ひ」と入力後、ListBox1に「ひ」が入っているすべてのの会社名を表示する
・検索値（英数大文字小文字、ひらがな、漢字、カタカナ）
Q2.ListBox1の値をクリック後ListBox2に売上の値を表示
・売上表示条件（同じ商品NO・同じ金額の場合＝一行にする
同じ商品NO・違う金額の場合＝そのまま表示する）
Q3.ListBox1の値をクリック後ListBox3に顧客シート13列目の値を表示
▼フォーム 顧客
CommandButton1 '入力 エクセルへ
CommandButton2 ’終了
TextBox1 'あいまい検索 ⇒ 顧客情報を引っ張ってくる
ListBox2 ’購入履歴 ⇒ 売上から購入商品を引っ張ってくる
ListBox3 ’注意 ⇒ 顧客から注意ランのデータを引っ張ってくる（データーは空欄のもある）
◆相談結果（ひなのメモ）
Dim posCt1 As Range
Dim posList1 As Range
Dim posCt2 As Range
Dim posList2 As Range
Dim posList4 As Range
Const COLS売上 As Long = 11
Const COLS顧客 As Long = 14
Dim orgBack As Long
Private Sub UserForm_Initialize()
'リストボックス1
With ListBox1
'リストBOXの中の列数と↓ここの列数が異なると実行後エラーになる
.ColumnCount = COLS顧客
.ColumnWidths = "30;90;0;0;150;80;200;0;0;0;0;0;0;0"
.BoundColumn = 5
orgBack = .BackColor
End With
'リストボックス2
With ListBox2
'作業シートの列NOを数える、売上の列番号と異なる
.ColumnCount = COLS売上
.ColumnWidths = "0;0;0;0;0;0;50;150;0;50;30"
End With
With Sheets("作業")
Set posCt1 = .Range("A1") 'ListBox1用抽出条件領域
Set posList1 = posCt1.Offset(, 2) 'ListBox1用リスト領域
Set posCt2 = posList1.Offset(, COLS顧客 + 1) 'ListBox2用抽出条件領域
Set posList2 = posCt2.Offset(, 2) 'ListBox2用リスト領域
End With
TextBox1_Change
End Sub
Private Sub TextBox1_Change()
Dim r As Range
Dim s1 As String
Dim s2 As String
With ListBox1
.RowSource = ""
.ColumnHeads = False
.BackColor = orgBack
End With
ResetList
With Sheets("顧客") '範囲
Set r = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 14)
End With
s1 = StrConv(TextBox1.Value, vbUpperCase) '文字列を大文字に変換
s2 = StrConv(TextBox1.Value, vbLowerCase) '文字列を小文字に変換
'フィルターオプションの条件、縦に並べると OR 条件 ANDはできない
With Sheets("作業") 'フィルター結果を作業シートにコピーする
.UsedRange.ClearContents
posCt1.Value = Sheets("顧客").Range("E1").Value '条件タイトル
'大文字小文字等で検索したい場合は設定
posCt1.Cells(2).Value = "*" & StrConv(s1, vbWide) & "*" '半角文字を全角文字に変換
posCt1.Cells(3).Value = "*" & StrConv(s1, vbNarrow) & "*" '全角文字を半角文字に変換
posCt1.Cells(4).Value = "*" & StrConv(s2, vbWide) & "*"
posCt1.Cells(5).Value = "*" & StrConv(s2, vbNarrow) & "*"
r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=posCt1.CurrentRegion, CopyToRange:=posList1, Unique:=False
If Not IsEmpty(posList1.Cells(2, 1)) Then
With posList1.CurrentRegion
ListBox1.RowSource = .Offset(1).Resize(.Rows.Count - 1).Address(External:=True)
ListBox1.ColumnHeads = True
End With
Else
ListBox1.BackColor = vbRed
End If
End With
End Sub
Private Sub ListBox1_Click()
Dim r As Range
ResetList
With Sheets("売上")
Set r = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 11)
End With
posList2.CurrentRegion.ClearContents
posCt2.Cells(1).Value = Sheets("売上").Range("D1").Value '抽出タイトル
posCt2.Cells(2).Value = ListBox1.Value '抽出条件（ListBox1で選ばれた１列目位の値）
posCt2.Cells(1, 2).Value = Sheets("売上").Range("E1").Value '抽出タイトル
posCt2.Cells(2, 2).Value = "<>0*" '抽出条件 0 ではじまらないもの
r.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=posCt2.CurrentRegion, CopyToRange:=posList2, Unique:=False
If Not IsEmpty(posList2.Cells(2, 1)) Then
posList2.CurrentRegion.Sort Order1:=xlAscending, Key1:=posList2.Columns(5), Order2:=xlDescending, Key2:=posList2.Columns(2), Header:=xlYes
posList2.CurrentRegion.RemoveDuplicates Columns:=Array(5, 8), Header:=xlYes
With posList2.CurrentRegion
ListBox2.RowSource = .Offset(1).Resize(.Rows.Count - 1).Address(External:=True)
ListBox2.ColumnHeads = True
ListBox3.List = Array(ListBox1.List(ListBox1.ListIndex, 12))
End With
Else
ListBox2.BackColor = vbRed
ListBox3.BackColor = vbRed
End If
End Sub
Private Sub ResetList()
With ListBox2
.RowSource = ""
.ColumnHeads = False
.BackColor = orgBack
End With
With ListBox3
.Clear
.BackColor = orgBack
End With
End Sub
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
すっかり忘却の彼方で、テストデータ等も、消してしまっていますので、キャッチアップに、少し時間がかかりそうです。 今日は、終日、外出することもあって、ちょっと時間ください。
その前に他の回答者さんから回答があればうれしいのですが。
（β） 2016/09/20(火) 06:32
データーお渡しします。
声をかけてください
（ひなの） 2016/09/20(火) 11:56
（β） 2016/09/20(火) 14:53
（ひなの） 2016/09/20(火) 19:16
■請求書シート
[A] [B] [C] [D] [E] [F] [G] [H] [12] NO 品名 1ｃｓ数量 数量ｃｓ 数量ｐｃ 単価 金額 備考 [13 0 [14] : [32]
■商品シート
[B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] …最終列 [2] NO 商品名前 容量 g x 数 pc 数量 重量 価格 001 020 035 [3] A01 マンゴ酒 400 g × 30 pc 400g×30pc 13 kｇ \100 90 [4] A02 なし酒 400 g × 30 pc 400g×30pc 13 kｇ \110 80 [5] A03 梅酒 400 g × 30 pc 400g×30pc 13 kｇ \120 [6] A04 杏酒 400 g × 30 pc 400g×30pc 13 kｇ \130 120 [7] A05 リンゴ酒 300 g × 20 pc 300g×20pc 7 kｇ \140 100 [8] A06 ぶどう酒 350 g × 20 pc 350g×20pc 8 kｇ \150 130 : 最終行
■フォーム名 商品_顧客
■変更箇所
現在の仕様 右側の顧客名をクリックすると 売上シート該当のデーターを表示する
それを 右側の顧客名をクリックすると 商品シートの該当する列を表示するこ とです。
データーを比較するのは全部NOであればうれしいです （ひなの） 2016/09/20(火) 19:31
ＤＬし、削除しておきました。 じっくりと拝見し思い出してみます。
（β） 2016/09/20(火) 19:35
私もあきらめずに、修正のチャレンジしてます。
思い通りの結果得られないですが( ┰_┰)
（ひなの） 2016/09/20(火) 20:09
少し時間が取れたので、以前のことを思い出しながら、DLしたブックのシートをチェックしました。 以下質問です。
１．TextBox1からListBox1への展開は従来通りですね。 ２．ListBox1 で選択された客先に対して、これと、請求書シートのB4を比較？？ B4 は請求書番号のようですが？ 客先は B5 だと思いますが、とにかく、これを比較？ ３．で、同じものなら、【客NOある時は該当する客NOの列をlistbox2 に表示】 この場合の該当する客NOの列って、どのシートのどの列ですか？ ４．同じではない場合は【客NOない時は商品K列をlistbox2 に表示】 商品シートのK列という意味ですか？ （ここは価格？） ５．上記 ３ または ４、ListBox2 には１列のみ表示ですか？（従来は複数列表示でしたけど）
（β） 2016/09/21(水) 21:41
2.請求書の B4は顧客NO B5は会社名 になってます。
現在は
比較対象は 請求書 B5会社名 ⇒ 比較先は 売上シート D列名前 になっています。
会社名で比較すると
途中で社名変更があると、直すのがすごく大変でした。
なので 今回は 顧客NOに変更できればと
比較対象は 請求書 B4 顧客NO ⇒ 比較先は 顧客シートの A列 になります。 商品シートは 1 行目になります。
請求書NO は G1 になります。
請求書の A4 を NO ⇒ 顧客NO に変更ください。 紛らわしいので(ー_ー)
3.4. Listbox2 の表示は 商品シートの内容↓こんな感じです。
該当の 客NOがない場合（この表示はフィルタクリアにも使いたいです。）
[B] [C] [I] [K] [2] NO 商品名前 数量 価格 [3] A01 マンゴ酒 400g×30pc \100 [4] A02 なし酒 400g×30pc \110 [5] A03 梅酒 400g×30pc \120 [6] A04 杏酒 400g×30pc \130 [7] A05 リンゴ酒 300g×20pc \140 [8] A06 ぶどう酒 350g×20pc \150
Listbox1 客NO 001 ?潟Aワビン クリック あるばあい（空欄は表示しない）
[B] [C] [I] [L] [2] NO 商品名前 数量 001 [3] A01 マンゴ酒 400g×30pc 90 [6] A04 杏酒 400g×30pc 120
可能ならフィルタではなく、今後編集にわかるよう
列とかわかるとすごくうれしいです ( ┰_┰)
こんなふうに。 .Range("A2").Value = ComboBox1.Value
私でも直せるといいな～(灬╹ω╹灬)┣¨ｷ┣¨ｷ
すみません。よろしくお願いします。
（ひなの） 2016/09/22(木) 23:52
今回は、なかなか、ピピピっときません。 何度も説明を読み、シートを見比べているのですが・・・・・
>>1.はい従来通りです。
従来、TextBox1 に あいまい検索用文字列を入れ、これを元に 顧客シートから ListBox1 に 顧客名を表示 ですね。 で、次には、この ListBox1 から 何か選び、選ばれた客先の売上データをListBox2 に表示。
こうでしたよね？
この選ばれたデータの顧客名ではなく、顧客ＮＯで関連情報をマッチングしたいというのはわかったんですが わからないのは 選んだデータの顧客番号 と 請求書シートの B4 との関係です。もしListBox1から選んだ顧客の番号が 111 だったとします。 で、請求書シートのB4 が 111 だったら、請求書シートの内容（商品の情報）を表示 ですかね？ 請求書シートの B4 が 111 じゃなかった場合は？ どこのシートの何を表示するのですか？ 商品シートにある情報すべてを表示？
（β） 2016/09/23(金) 19:34
その前に先ず 商品シート の 値をないしてください。 その方がわかり易いと思います。
[L] [M] [N] [2] 001 003 004
（ひなの） 2016/09/23(金) 22:10
1.エクセルシートの概要
請求料シート へ入力 顧客情報は ⇒ 顧客シートへ登録 顧客NOと価格は ⇒ 商品シートの2列の最終列に登録 売上の詳細は ⇒ 売上シートに登録
▲という仕様になってます（未来の完成図です。）
◇顧客シートに顧客NO 有 で 商品シートも顧客NO 有 の場合。 商品を一回でも購入する履歴ある時のみ
◇顧客シートに顧客NO 有 で 商品シートに 顧客NO 無 の場合。 顧客情報のみ登録 購入履歴なし。
◇顧客シートに顧客NO 無 で 商品シートに 顧客NO 無 の場合。 新規客（この場合はフォーム使わないので特に問題ありません。） 2.フォームの使用
フォーム名 「商品_顧客」オレンジ色です。（顧客のフォームと商品のフォームを合体させた感じです。）
■やりたいこと TextBox1 英数字大文字小文字 ひらがな カタカナ であいまい検索 値入力して、ENTER 押すと検索結果があるときListBox1に表示 検索結果の値がないとき「赤」色にする ListBox1 表示データーの元は 顧客シートの値 ListBox1の顧客NOをクリックすると ListBox2 に該当する商品シートのデーターを表示
IF ListBox1 0列目（エクセルシートの顧客NOは1列目） ＝ 商品シート 該当する列の顧客NO Then
ListBox2 に 商品シートの B列 ｃ列 I列 該当の顧客列
ないとき ListBox2 に 商品シートの B列 ｃ列 I列 K列（価格）
ListBox2 表示データーの元は 商品シートの値
ざっくりこんな説明ですが、伝わりますでしょうか (pゝД；｀q)
いつも説明下手で申し訳ないです。
（ひなの） 2016/09/23(金) 23:12
すっかり勘違いをしていたようです。
まず、今まで作ってきたものの仕様を変更するんだと思っていましたが、そうではなく、全く新しいユーザーフォームをつくる。 その際に、今までの仕掛けのなかの部品を利用する という感じなんですね。
それと、なぜ 【請求書シート】のB4 とマッチングしなければいけないのか、それが、どうしてもわからなかったのですが （というか、操作として、あいまい検索したものから顧客を決定した後に、１枚しかない請求書シートの顧客番号と比較するという流れというか必要性が理解できなかった） 実際には請求書シートとの比較は不要なんですね？ でも、もし、顧客番号が同じなら、売上シートから該当顧客分を抽出しなくても、請求書シートに存在するので それを、そのまま表示すればいいと、そういうことでしたか？
商品シートとのマッチングも、顧客番号と商品シートを、なぜ、どのように比較するんだろうと思っていましたが 商品シートのL列から右に顧客の実績列があったんですね。 で、この顧客列に値があるものを表示ということだったんでしょうか？
ところで商品シートの顧客列の数字は、売価ですか？
追加で。
>>該当の 客NOがない場合（この表示はフィルタクリアにも使いたいです。）
フィルタクリアにも使いたい というところの意味が、まだ理解できていません。
>>可能ならフィルタではなく、今後編集にわかるよう 列とかわかるとすごくうれしいです
この意味は、従来、フィルターオプションを使ったコードを提示してきましたが そうではない方法でコード記述したい（フィルターオプションは使いたくない）ということですか？ もちろん、フィルター関連処理ではなく、Dictionaryなどを使ったメモリー内処理を書くことはできますが。 （この場合、従来 ListBoxの先頭に表示していたタイトル行はなくなるかも。）
（β） 2016/09/24(土) 06:28
>実際には請求書シートとの比較は不要なんですね？ はい。
>顧客番号が同じなら、売上シートから該当顧客分を抽出しなくても、請求書シートに存在するのでそのまま表示すればいいと、そういうことでしたか？ 請求料シート へ入力 顧客情報は ⇒ 顧客シートへ登録 顧客NOと価格は ⇒ 商品シートの2列の最終列に登録 売上の詳細は ⇒ 売上シートに登録
▲これは、あくまでエクセルのマクロの作動です「売上へ登録」のやつ （ホントはフォームにボタン設定して操作したかったですが、やり方わからなかったので、そのままにしました ( ┰_┰)）
3.請求料へ入力する流れ 「商品_顧客」クリック 該当の顧客探す ⇒ 請求料へ転記 ListBox1の該当顧客 クリック → ListBox2 購入履歴調べる ある場合 ⇒ 請求書 へ転記 ない場合 ⇒ 全商品のリストで該当商品クリック ⇒ 請求書 へ転記
>顧客番号が同じなら、売上シートから該当顧客分を抽出しなくても、請求書シートに存在するので それを、そのまま表示すればいいと、そういうことでしたか？ ？？？？何を表示るんでしょうか？？
> 商品シートのL列から右に顧客の実績列で、この顧客列に値があるものを表示ということだったんでしょうか？ はい、そうです。
>ところで商品シートの顧客列の数字は、売価ですか？ はい、そうです。
> >>該当の 客NOがない場合（この表示はフィルタクリアにも使いたいです。） すみません。商品リスト全商品とK列の価格が見れるならOKです。フィルターは忘れてください。
Private Sub ComboBox1_Change()。に入れようと思ってます。
商品がA～F,Z,Oの分類あるので、フォーム「商品」のように、分類別に見れるようにできればなと思ってます。
>Dictionaryなどを使ったメモリー内処理を書くことはできますが。 はい。よろしくお願いします。
>（この場合、従来 ListBoxの先頭に表示していたタイトル行はなくなるかも。） はい、タイトルは問題ないです。あればいいな～といった程度です。内容で大体わかります。
すみません。こんな感じです。1っ箇所だけわからなかったですが、 もう一度説明していただけると嬉しいです (灬╹ω╹灬) よろしくお願いします。
（ひなの） 2016/09/24(土) 09:07
だいぶ、理解が進みました。でも、あらたに ？？ となったところもあります。
新たに ？？ となったところ ３点。
本トピは ＤＬ したブックの中の 商品_顧客 フォーム を完成させることだと、そう理解しました。 （コメントしたように、それまでは、ずっと 従来の 顧客情報 フォームの手直しかと思っていました。） ですけど、『商品がA～F,Z,Oの分類あるので、フォーム「商品」のように、分類別に見れるようにできればなと思ってます。』 という説明がでてきて、フォーム『商品』って、いままで、対応してこなかったもので、中身がどんなものなのか わかっていないのですが、【分類別】というのが、どんな表示なのか、それが 新たに ？？？ となっています。
次に、【Private Sub ComboBox1_Change()。に入れようと思ってます。】 何をどこにいれるのか、理解できていませんが、それ以前に、ComboBox1 は 商品_顧客 フォームには存在しませんよね？ TextBox1 は 顧客を絞り込むためのあいまい検索用文字列入力で、絞り込んだ結果をListBox1に表示。 ListBox1 で選んだ顧客は、ListBox2 表示用のキーとして使いますよね。 ComboBox1 とは ？？
最後に、【請求料へ入力する流れ】。別途登場する請求料シートというのも、どんなシートはわからないのですが 今回対応する 顧客_商品 フォームのロジックで、この流れって、コード化必要ですか？ それとも、単に 参考情報として説明していただいているということですか？
で、そちらが、わからなかったという１か所。これも、私が誤解しているのかもしれないんですが。
ListBox1 で選択した顧客の顧客番号で、何をどんな優先順位で検索して、何を ListBox2 に表示するのかという根本的なところですけど
・もし、この顧客NO が 請求書シートの B4 と同じであれば請求書シートに記載されている商品情報を表示 ・そうでなかったら、商品シートの顧客列を検索し、あれば、その列に値のある行を表示 ・いずれもなかったら・・・どうするかなぁ・・・？
こんな理解なんです。間違っていますか？
（β） 2016/09/24(土) 09:46
すみません。説明は細くも入っています。 あと説明が下手ですみません。 今回はフォームのみでお願いします。 ＼(･_＼)請求書のB4は (／_･)／コッチニオイトイテください。
◆やりたいことのまとめ
TextBox1 英数字大文字小文字 ひらがな カタカナ であいまい検索
値入力して、ENTER 押すと検索結果があるときListBox1に表示
検索結果の値がないとき「赤」色にする
ListBox1 表示データーの元は 顧客シートの値
ListBox1の顧客NOをクリックすると ListBox2 に該当する商品シートのデーターを表示
IF ListBox1 0列目（エクセルシートの顧客NOは1列目） ＝ 商品シート 該当する列の顧客NO Then
ListBox2 に 商品シートの B列 ｃ列 I列 該当の顧客列
ないとき
ListBox2 に 商品シートの B列 ｃ列 I列 K列（価格）
ListBox2 表示データーの元は 商品シートの値
▼下記の各シートデーターと ListBox1とListBox2 と同じデーターと思ってください。説明がわかり易いかなと
■顧客シート = ListBox1
[A] [B] [C] [D] [E] [1] 顧客NO 電話 携帯 FAX 社名 [2] 001 (株)アワビン ← ★クリックB [3] 002 アマエビン(株) ← ★クリックA [4] 003 (株)イソガエン [5] 004 マグロン(株) [6] 005 (株)カニン
■商品シート = ListBox2 L列 以降が各顧客の価格になります。
[B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] …最終列 [2] NO 商品名前 容量 g x 数 pc 数量 重量 価格 001 003 004 [3] A01 マンゴ酒 400 g × 30 pc 400g×30pc 13 kｇ \100 90 [4] A02 なし酒 400 g × 30 pc 400g×30pc 13 kｇ \110 80 [5] A03 梅酒 400 g × 30 pc 400g×30pc 13 kｇ \120 [6] A04 杏酒 400 g × 30 pc 400g×30pc 13 kｇ \130 120 [7] A05 リンゴ酒 300 g × 20 pc 300g×20pc 7 kｇ \140 100 [8] A06 ぶどう酒 350 g × 20 pc 350g×20pc 8 kｇ \150 130 : 最終行
■フォーム 【商品_顧客】 TextBox1 ListBox1 ListBox2
**************************************************************************************************************
＞ListBox1 で選択した顧客の顧客番号で、何をどんな優先順位で検索して、何を ListBox2 に表示するのかという根本的なところですけど
下記は例を使ってみました。
TextBox1に (株)と入力すると 上記の ■顧客シート = ListBox1 のデーターが ListBox1に表示される（多分）
▼ListBox1のとこの★マークをクリックすると ★クリックA の行をクリックする場合 顧客NO.002 → 商品シート = ListBox2 2行目 どこ見ても「002」なし ⇒ ListBox2に表示A ★クリックB の行をクリックする場合 顧客NO.001 → 商品シート = ListBox2 2行目 の「L列」に同じ顧客NO有 ⇒ ListBox2に表示B
■ListBox2 表示A （商品シートのデーター）
[B] [C] [I] [K] [2] NO 商品名前 数量 価格 [3] A01 マンゴ酒 400g×30pc \100 [4] A02 なし酒 400g×30pc \110 [5] A03 梅酒 400g×30pc \120 [6] A04 杏酒 400g×30pc \130 [7] A05 リンゴ酒 300g×20pc \140 [8] A06 ぶどう酒 350g×20pc \150
■ListBox2 表示B （商品シートのデーター）空欄以外 [B] [C] [I] [L] [2] NO 商品名前 数量 001 [3] A01 マンゴ酒 400g×30pc 90 [6] A04 杏酒 400g×30pc 120
長くなってすみません。こんな感じ大丈夫でしょうか？
（ひなの） 2016/09/25(日) 00:13
なんとなくわかってきました。 で、悩んでいても進まないので、とにかく 理解したベースで書いてみます。 しばらく時間ください。
で、１点質問をアップしておきます。コードを書き上げるのは、まだしばらく先ですが、それまでに返事いただければありがたいです。
>>可能ならフィルタではなく、今後編集にわかるよう 列とかわかるとすごくうれしいです
この文の意図をもう一度確認。
フィルタ機能を使いたくない？ 作業シートを使いたくない？ あるいは両方使いたくない？ 本件、メモリー内で抽出されたものを展開し、それを直接 ListBox2 の List に格納することで フィルターオプションなしで、かつ作業シートなしでコードを書くことはできます。 できますが、現在、ListBox2 に表示している先頭のタイトル行、これが表示できなくなります。 先頭のタイトル行は、シート上にタイトル行があるリストイメージをつくり、その領域を RowSourceで与えることで表示されます。
今のところフィルターじゃない方式でメモリー内でリストを生成し、それをいったん 作業シートに転記して RowSourceとして指定することを考えています。
（β） 2016/09/25(日) 18:15
ちょっと眠い状態で書きましたので、すっきりしない部分もありますがとりあえず。
商品_顧客 フォームの
●Initialize 内の 'リストボックス2 の中の
'リストボックス2
With ListBox2
'作業シートの列NOを数える、売上の列番号と異なる
.ColumnCount = COLS売上
.ColumnWidths = "0;0;0;0;0;0;50;200;0;35;35;35"
End With
これを
'リストボックス2
With ListBox2
.ColumnCount = 4
.ColumnWidths = "25;50;100;35"
End With
に変更。（列幅は、調整してください）
●ListBox1_Click を以下で置き換え
Private Sub ListBox1_Click()
Dim shC As Worksheet
Dim r As Range
Dim c As Range
Dim w As Variant
Dim x As Long
Dim s As String
ResetList
Set shC = Sheets("商品")
Set r = shC.Range("L2", shC.Range("K2").End(xlToRight)).Find(What:=ListBox1.List(ListBox1.ListIndex, 0), LookAt:=xlWhole)
posList2.CurrentRegion.ClearContents
With shC.Range("B2").CurrentRegion.EntireRow
If r Is Nothing Then
posList2.Resize(.Rows.Count).Value = .Columns("B").Value
posList2.Offset(, 1).Resize(.Rows.Count).Value = .Columns("C").Value
posList2.Offset(, 2).Resize(.Rows.Count).Value = .Columns("I").Value
posList2.Offset(, 3).Resize(.Rows.Count).Value = .Columns("K").Value
Else
ReDim w(1 To .Rows.Count)
For Each c In Intersect(.Cells, r.EntireColumn)
If Not IsEmpty(c) Then
x = x + 1
s = IIf(x = 1, c.EntireRow.Columns("K").Value, c.Value)
w(x) = Array(c.EntireRow.Columns("B").Value, c.EntireRow.Columns("C").Value, c.EntireRow.Columns("I").Value, s)
End If
Next
ReDim Preserve w(1 To x)
posList2.Resize(UBound(w), 4).Value = WorksheetFunction.Transpose(WorksheetFunction.Transpose(w))
End If
End With
If Not IsEmpty(posList2.Cells(2, 1)) Then
With posList2.CurrentRegion
ListBox2.RowSource = .Offset(1).Resize(.Rows.Count - 1).Address(External:=True)
ListBox2.ColumnHeads = True
End With
Else
ListBox2.BackColor = vbRed
End If
End Sub
（β） 2016/09/25(日) 22:17
>フィルタ機能を使いたくない？
使いたくないのではなく、使い方がわからないので。 セルとか列がわかると、ちょいと直しや。 なんとなく、ここがこんな使い方か～みたなのわかるんですが。
フィルタはシステムで自動なので、分からなかったです ( ┰_┰) ちょっと、行をずらしたくてもできませんでした。。・ﾟ・(pゝД；｀q)・ﾟ・ すみません。理解度が低くて。
そんならまだDictionaryのほうが 直せてたな～と
理由は単純です。行や列がずれた場合。直し方がわからないから。。。。。。。。。
>作業シートを使いたくない？
特に問題ありません。 今も使ってますので（〃⌒ー⌒〃）
すみません。変なとこで悩ませてしまいました。
頂いたコードを試してみますね少しお時間下さい。 （ひなの） 2016/09/26(月) 18:24
Q2. マクロでは Call マクロ1 とか 違うところのマクロを呼び出すことができますが ユーザーフォームは Sub ListBox1_Click() Call Sub TextBox1_Change() とかコードを呼び出すことはできますか？ （ひなの） 2016/09/26(月) 18:27
はい。呼び出せますよ。 引数付のものは、ちゃんとした引数も指定して呼び出すことが必要ですが。 Sub ListBox1_Click() やl Sub TextBox1_Change() は引数がないので、何も考えず、そのまま 呼び出せますね。
というか、現在のコードの Initialize プロシジャの最後、 End Sub の上を見てください。 すでに、TextBox1_Change を呼び出していますよ。
（β） 2016/09/26(月) 18:42
以下は、雑談です。時間があった時に眺めてください。
>>セルとか列がわかると、ちょいと直しや。 >>なんとなく、ここがこんな使い方か～みたなのわかるんですが。 >>フィルタはシステムで自動なので、分からなかったです ( ┰_┰) >>ちょっと、行をずらしたくてもできませんでした。。・ﾟ・(pゝД；｀q)・ﾟ・
フィルターオプションの名誉のために弁護（？）しておきます。
フィルターオプションは、既存リストから必要な列を必要な条件を与えて、場合によっては、重複も排除して取り出すという処理（作業であれＶＢＡであれ）をするにあたって 「非常に効率がよく」、かつ、ＶＢＡとしても「非常にシンプルでわかりやすい１行コード」で実行可能です。
わかりにくいとおっしゃるのは、フィルターオプションの部分ではなく、既存リストの領域特定の部分や、それに与える条件欄、抽出リストの作業シート上の位置の規定が βが提示したコードではわかりにくい、つまり、βのコードが悪いのであって、フィルターオプションのせいではありません。
ちなみに、（β） 2016/09/25(日) 22:17 でアップしたコードの中の ListBox1_Click をフィルターオプション版にすると 以下になります。
Private Sub ListBox1_Click()
Dim shC As Worksheet
Dim r As Range
Dim c As Range
Dim w As Variant
Dim x As Long
Dim s As String
Dim t As String
ResetList
Set shC = Sheets("商品")
Set r = shC.Range("L2", shC.Range("K2").End(xlToRight)).Find(What:=ListBox1.List(ListBox1.ListIndex, 0), LookAt:=xlWhole)
posList2.CurrentRegion.ClearContents
posCt2.ClearContents
If r Is Nothing Then
t = shC.Range("K2").Value
posCt2.Cells(2).ClearContents
Else
t = r.Value
posCt2.Cells(2) = "=" & r.EntireColumn.Cells(3).Address(False, False) & "<>"""""
End If
posList2.Resize(, 4).Value = Array(shC.Range("B2").Value, shC.Range("C2").Value, shC.Range("I2").Value, t)
shC.Range("B2").CurrentRegion.AdvancedFilter xlFilterCopy, posCt2.Resize(2), posList2.CurrentRegion.Rows(1)
If Not r Is Nothing Then posList2.Cells(1, 4).Value = shC.Range("K2").Value
If Not IsEmpty(posList2.Cells(2, 1)) Then
With posList2.CurrentRegion
ListBox2.RowSource = .Offset(1).Resize(.Rows.Count - 1).Address(External:=True)
ListBox2.ColumnHeads = True
End With
Else
ListBox2.BackColor = vbRed
End If
End Sub
（β） 2016/09/27(火) 10:30
ありがとうございます。
今ばたばたして、なかなか見る時間作れず
返信できなくてすみません。
金曜に時間できるので、ゆっくり見ますね。
ちなみ、だれも悪くないですよ(*˘︶˘*)
件かは得ていますから、どのコードの～♪
（ひなの） 2016/09/27(火) 17:12
（β）様
ありがとうございます。いつもながら完璧です ( ✧Д✧) しかも、私のわがままは反映され。セルとかがいっぱいあります。 すごくうれしいです。
コードでいくつか質問させてください。 お時間あるときで構いませんので教えていただけると嬉しいです。 よろしくお願いしします。
Q1. Set r = shC.Range("L2", shC.Range("K2").End(xlToRight)).Find(What:=ListBox1.List(ListBox1.ListIndex, 0), LookAt:=xlWhole)
Set r = shC.Range(★？, K2から最終列).Find(と同じインデクスの名前が見つかったかどうかを判定する)
Application関数のような役割であってますか？
意味は
商品シートK2～最終列とListBox1が同じIndexを探す
であってますか？
ただ、その前のL2は何の意味でしょうか？調べてもわからなかったです ( ？_？) （ひなの ） 2016/09/30(金) 19:54
>>Application関数のような役割であってますか？
そうですね。Application.Match と同じようなことを Findメソッドでやっています。
検索領域は L2 から 2行目のデータ最終列までということにしています。 最初の "L2" は セルの L2 のことで、Range(開始セル,終了セル) という領域規定の 開始セルが "L2" ということです。
（β） 2016/09/30(金) 20:22
ありがとうございます。
なるほどそういうやり方もあるんですね。
List1の値が 商品シート L2 から 2行目のデータ最終列 にあるかを探すと。
Q2.ザックリですが意味あってますか？
'商品シートB2列からデータの入力されているセル領域の終端セルを参照
With shC.Range("B2").CurrentRegion.EntireRow
'顧客がない場合
If r Is Nothing Then
'List2の最終行に ＝ 商品シート 商品NO を格納
posList2.Resize(.Rows.Count).Value = .Columns("B").Value
'List2（左から1列飛ばして） 1列目 に ＝ 商品シート 商品名 を格納
posList2.Offset(, 1).Resize(.Rows.Count).Value = .Columns("C").Value
'List2（左から2列飛ばして） 2列目 に ＝ 商品シート 数量 を格納
posList2.Offset(, 2).Resize(.Rows.Count).Value = .Columns("I").Value
'List2（左から3列飛ばして） 3列目 に ＝ 商品シート 価格 を格納
posList2.Offset(, 3).Resize(.Rows.Count).Value = .Columns("K").Value
（ひなの ） 2016/09/30(金) 20:53
まず、今まで特に確認はしていませんでしたが、商品シートの1行目とA列は空白であるという前提です。 (DLしたシートがそうなっていましたので）
なので、Range("B2").CurrentRegion で、B2から始まる表の領域を取得できています。
もし、1行目やA列に何か値があるセルが存在すれば、1行目やA列も表の一部だとみなされます。
CurrentRegion の定義はおわかりですよね。（必要なら補足説明します）
'商品シートB2列からデータの入力されているセル領域の終端セルを参照
With shC.Range("B2").CurrentRegion.EntireRow
●shC.Range("B2").CurrentRegion が表全体の領域です。Range("B2:N8") といった感じですね。
で、その.Entirerow ですから、2行目から8行目までの行全体、つまり、$2:$8 という領域です。
B列から始まる領域のままだと、実際のB列を B という表現で指定できず、たとえば .Columns(1) あるいは .Columns("A") などという ？？ といった表現になり
わかりにくくなるので、あえて領域を A列から始まるものにしています。それによって、.Columns("B") と記述すると本当の B列を参照できます。
●ということで、顧客がない場合に
'List2の最終行に ＝ 商品シート 商品NO を格納
posList2.Resize(.Rows.Count).Value = .Columns("B").Value
これは posList2（単一セルです）の .Rows.Count行、つまり With でくくった $2:$8 の行数である 7行の領域に .Columns("B") これは 商品シートの B2:B8 ですけど、
この内容を書きこんでいます。
同様に、たとえば
'List2（左から3列飛ばして） 3列目 に ＝ 商品シート 価格 を格納
posList2.Offset(, 3).Resize(.Rows.Count).Value = .Columns("K").Value
これは、posList2という単独セルの３列右（posList2も含めると４列目）に 商品シートの K2:K8 を書きこんでいます。
う～ん・・・・ちょっと 難しいですかね？
（β） 2016/09/30(金) 21:26
時間があるときに以下のコードを実行してみてください。
最初のコードでは C列、あとの３つのコードでは B列になっています。 結果をみて、コードをじぃぃっと見つめなおすと、前のレスで述べたことがわかってもらえるかも。
Sub Test()
Dim r As Range
Set r = Range("B2:N8")
MsgBox r.Columns("B").Address
MsgBox r.Columns(1).Address
MsgBox r.Columns("A").Address
MsgBox r.EntireRow.Columns("B").Address
End Sub
（β） 2016/09/30(金) 21:32
（β）様 ありがとうございます。
すみません。 勘違いました。
ListBox の始まりは 0列 1列 2列 なので、
posList2 単独なので、 エクセルと同じ数え方になるんですね
posList2 商品シートの K2:K8 を書きこんでるだけで Listbox2 に K2:K8 を書きこんでるわけではないですもんね。
結構勘違いしてました( ┰_┰)
posList2 は 値を一時的に保管する倉庫みたいなもんですね
＞CurrentRegion の定義はおわかりですよね。 はデーターのある前範囲のセルの値を取得する。途中に空欄があるとそこで止まる。と書いていります。 http://qiita.com/furico/items/e2155fdd8810dd79795a
>時間があるときに以下のコードを実行してみてください。
あれ～ MsgBox r.Columns("B").Address 予想外の結果です。
(；￢д￢)ジー―――と見てみます
（ひなの ） 2016/09/30(金) 21:59
ジーッとみるときの参考として。
たとえば Columns("B") とか Range("B5") というコードを見ると、我々は、とっさに
あぁ、あの、エクセルシートの左のほうの B 列のことだとか、左上のほうにある、あの B列の5行目のセルだと
そう思いますね。間違ってはいません。Columns("B") とか Range("B5") だけなら、その通りですから。
でも、実は Columns("B") とか Range("B5") は、ある領域の.Columns("B") とか ある領域の.Range("B5") であり
そこが省略されていると、シートのセル全体の領域の.Columns("B") あるいは シートのセル全体の領域の.Range("B5") になります。
で、この "B" は、とっさにイメージする【あのB列】ではなく、ある領域の中の 「2番目の列」という意味の B なんです。 5 も、シートの上から数えて5行目ではなく、ある領域の 5行目 という意味です。
で、シートのセル全体の領域の ということなので B も 5 も 「たまたま、我々が、とっさにイメージするものと」同じになるということです。
一方、Range("B2:N8").Columns("B") は B2:N8 という領域の 2列目の領域、つまり C2:C8 になりますし、Range("B2:N8").Range("B5") は
B2:N8 という領域の 2列目の5行目 となって、C6 になります。
なんで、こんな紛らわしい記述方法があるんだ！ と思われるかもしれませんが、これはこれで、便利な利用方法もあります。 でも、通常は、わかりづらくなりますよね。
なので、B2:N8 の中の特定の列を取り出したいのですが、われわれがイメージする列記号で指定できるように
Range(B2:N8").EntireRow これは エクセルシートの 2行目から8行目の領域ですので Range(B2:N8").EntireRow.Columns("B") と記述すると
エクセルシートの2行目から8行目の2番目の列、つまり B2:B8 を参照できると、まぁ、そんなところですね。
（β） 2016/10/01(土) 09:01
もう、おなか一杯だと思いますけど、１つだけ。
私のコードでは With shC.Range("B2").CurrentRegion とくくって、この領域を参照する記述で書き始めましたので
アップしたような領域表現にしましたが、領域の指定は、とにかく、いろんな方法で記述できます。
バリエーションがありすぎて、かえって困る？ というぐらい、様々な方法があります。
たとえば 行変数 = shC.Range("B2").CurrentRegion.Rows.Count と記述して領域の行数を取得しておいて
shC.Range("B2").ReSize(行変数) これでも、CurrentRegion が B2:N8 なら B2:B8 を参照できます。
このほうが、ひなのさんにとって わかりやすかったかもしれませんね。
（β） 2016/10/01(土) 09:17
（β）様
ありがとうございます (；￢д￢)ジー――――――――――――――と見つめて 説明文と照らし合わせたところようやくわかりました。
これで何とかやってみます。 またわからないことありましたら、懲りずに教えていただけましたらうれしいです。 ホントにありがとうございました。 （ひなの ） 2016/10/02(日) 22:10
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.