数値（コード番号）によってリストへの表示を分ける

質問

よろしくお願いします。

C列はコード番号（100から200）コード番号は変動あり
D列は項目
コード番号が１00から150なら、コード番号と右隣の項目をリストとして、支出リスト（ユーザーフォームのリストボックス）に２列表示する。

コード番号が１51から200なら、コード番号と右隣の項目をリストとして、支出収入リスト（ユーザーフォームのリストボックス）に表示する。

With Me.支出リスト
.RowSource = Worksheets("AAA").Range("C1:D100").Address(External:=True)
End With
With Me.収入リスト
.RowSource = Worksheets("AAA").Range("C1:D100").Address(External:=True)
End With

回答No.2

real beatin（@realbeatin） · ベストアンサー

こんにちは。
方法は幾つかありますが、VBAのリストの扱い方として
基本通りのやり方として
' ' //
Dim i As Long
　　Worksheets("AAA").Select
　　For i = 1 To Cells(1, "C").End(xlDown).Row
　　　　Select Case Cells(i, "C")
　　　　Case 100 To 150
　　　　　　Me.支出リスト.AddItem Cells(i, "C")
　　　　　　Me.支出リスト.List(Me.支出リスト.ListCount - 1, 1) = Cells(i, "D")
　　　　Case 151 To 200
　　　　　　Me.収入リスト.AddItem Cells(i, "C")
　　　　　　Me.収入リスト.List(Me.収入リスト.ListCount - 1, 1) = Cells(i, "D")
　　　　End Select
　　Next i
' ' //
事前に、各ListBoxについて、デザイン画面にて
　ColumnCount　2
　ColumnWidths 30 pt;100 pt
　RowSource　（空）
等、必要な設定を追加、変更、確認しておいて下さい。

質問の文脈として、期待されているものは、
.RowSource に対して、
クエリや関数のような抽出コマンドを指定できるのではないか？
ということのように思われますが、
.RowSource に指定できるのは、単純に、
単矩形範囲（ひとつの連続した四角い範囲）
のみ、です。

したがって、
VBAの処理として、条件分岐を経て抽出したものを
それぞれのListBoxに格納することになります。
　Select Case ステートメント
　ListBox.AddItem メソッド
　ListBox.List プロパティ
の３点、理解に自信ないようでしたら、ヘルプ等でお浚いしておいて下さい。

他の方法としては、
配列変数を使って、.Listプロパティをまるごと直接設定する方法など、
場合によっては、より効率的になることもありますが、
難度が高いものですので、今回は、
基礎的な、.AddItemメソッドを提示するに留めます。

より易しい（解り易い）方法については、
一旦、VBAのことは忘れてもらって、
抽出ということなら、
Excelの[フィルター]機能や[フィルターの詳細設定]機能
が、得意とするものですので、
こちらを使うのが易しいかな、と思います。
VBAで難しいことを考える前に、
少しばかりExcelの基本機能をお浚いしておくのも大切です。
例えば、C1:D1 に項目タイトルがあって、
C2:D101 にデータがある、というような設計の基本を守るだけで、
使える機能が増えてきます。
　・１行めは項目タイトル
　・[フィルター]
　　→[数値フィルター]
　　　→[指定の範囲内]
　　　　→[100] [以上]
　　　　　[AND]
　　　　　[150] [以下]
　　　　　→[OK]
　・フィルターで得られた結果を空のシートにコピペ
　・Me.支出リスト.List = 貼り付けられた抽出後のセル範囲の.Value
みたいなやり方、この方が、一般には人気あるみたいですね。
（マクロの記録で基本コードを得られますし）
ただ、今回ご提示の条件そのままでは手を出し難いですね。
VBAはExcelシートのデザインに従うことしかできませんから、
覚えながら、シートデザインを標準化していくことにも
意識を向けてあげると、いいことあるかも、です。

蛇足ですが、
もしかして、コード番号順に並べ替えた結果をリストにしたい
というような場合だったら、Excelの[並べ替え機能]を中心に組めば、
単矩形範囲２つをそのまま.Listに指定するだけで済みますから、
さらに簡単に出来そうですね。
質問を読む人は、もしかして、を考えますから、
何が正解か見つけるの難しかったり、
保険を掛けて難しい方法を敢えて選んだり、します。
ご自身の"やりたい事"を、明確に限定的に言葉にする習慣づけを
大事にしていると、VBAも簡単に言うこと聴いてくれるようになるので、
"やりたい事"はなんなのか、考えては書き、この繰り返しに
慣れて行けば、上達も早くなっていいのかも、です。

以上、ご参考まで。

お礼

realbeatin様
詳細な解説ありがとうございます。
とても勉強になります。
解決しました。

回答No.4

imogasi

#1です。
ＤａｔａＳｏｕｒｃｅについて、配列利用・法でやってみました。
下記コードのUserForm1.ListBox1.List() = Aのあたりが特徴です。
ーー
質問とは、２列に振り分ける区分点が、質問と違ったままにしていますが
コードを修正してください。
ーー
データ例
Ｓｈｅｅｔ１　Ｃ１：Ｄ７
コード 科目
121 あ1
131 あ2
142 あ3
161 あ4
175 あ5
184 あ6
ーー
操作など、メニュー選択など
ーー
開発
ＶｉｓｕａｌＢａｓｉｃ
挿入
ユーザーフォーム
ユーザーフォームにリストボックス２つ貼り付け
Ｌｉｓｔｂｏｘ１をクリック
右クリック
プロパティでＣｏｌｕｍｎｃｏｕｎｔを２（２列）
ユーザーフォームの「コードの表示」
各リストボックスの第I列、第2列の表示幅を十分広げる（手動）。またはコードで設定もできる（略）
Ｌｉｓｔｂｏｘ２についても同じ。
ーーーー
コードは、ユーザーフォームでコードの表示で、Ｉｎｉｔｉａｌｉｚｅイベントにコードを書く。
Private Sub UserForm_Initialize()
Dim A(1000, 2)
Dim B(1000, 2)
'--
k = 1
l = 1
lr = Worksheets("Sheet1").Range("C100").End(xlUp).Row
MsgBox lr
'--
For i = 2 To lr
v1 = Worksheets("Sheet1").Range("C" & i)
v2 = Worksheets("Sheet1").Range("D" & i)
If Worksheets("Sheet1").Cells(i, "C") < 150 Then
A(k, 0) = v1
A(k, 1) = v2
k = k + 1
Else
B(l, 0) = v1
B(l, 1) = v2
l = l + 1
End If
Next i
UserForm1.ListBox1.List() = A
UserForm1.ListBox2.List() = B
End Sub
（注意）
下記は適宜修正
・シート名の指定
・配列のデータ要素の限度
・リストボックスを収入と支出に振り分けるコード部分（上記では１５０で分けたが）

お礼

imogasi様
早速のご回答ありがとうございます。
解決しました。

回答No.3

real beatin（@realbeatin）

あ、
並べ替えしてもいいのでしたら、例えばこんな感じで。
' ' //
Dim i As Long
　　With Worksheets("AAA")
　　　　With .Range("C1:D" & .Cells(1, "C").End(xlDown).Row)
　　　　　　.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
　　　　　　i = WorksheetFunction.Match(150, .Columns(1).Cells, 1)
　　　　　　Me.支出リスト.List = .Resize(i).Value
　　　　　　Me.収入リスト.List = .Rows(i + 1).Resize(.Rows.Count - i).Cells.Value
　　　　End With
　　End With
' ' //
後から＃１補足に気が付きました。失礼。

お礼

realbeatin様
早速のご回答ありがとうございます。
解決しました。

回答No.1

imogasi

丁寧に質問を文章表現せよ。 補足を考えてみて。
ユーザーフォームに左右？または上下２つのリストボックスを設ける（または、設けてあるとする）
あるワークシートのＣ列にはコード番号（３けた
、そして対応する（隣の）Ｄ列には対応する項目文字列があるということだな。
収入項目も、支出項目も行的に混じっているのだな。
するとどういう風に収入と支出を振り分ければよいのか。
収入のコードと支出のコードが別列に振り分けてないと、どうして振り分けるのか。コードに区分コード１桁？を含んでいないような場合です。
人間のように、知識や経験や常識をプログラムで持たせることは出来ないのは、わかっているだろう。
ーー
上記は小生の質問の読みが間違っているか。
一括して、RowSource で設定できない場合は、２系列の配列に前もって振り分けるか、１行ずつ支出か収入か判別して、どちらかのリストボックスにＡｄｄＩｔｅｍすることになろう。
ーー
もう一つ問題点があろう。」
Ｃ、Ｄ列のコードの増減や変更に即座に対応するということになれば、むつかしいよ。シートのＣｈａｎｇｅイベントというのがあるが、うまく使えるかな。

補足

imogasi様　ご回答ありがとうございます。
説明になるかどうかわかりませんがよろしくお願いします。

今、使用している、C列のコード番号は、100から130および151から170です。
131,132,133と新規に増えた場合は最終行の1つ下に入力し、C列の数値でC列とD列の並べ替えをなっています。
その時にC列の値が150以下なら支出リストに、151以上の値なら収入リストに表示するようにしたいと思っています。