Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


1517 / 13644 ツリー ←次へ | 前へ→

【73862】ユーザーフォームで複数条件で検索しシート上にフィルタかけるマクロ 大渕 13/2/25(月) 23:53 質問[未読]
【73863】Re:ユーザーフォームで複数条件で検索しシ... kanabun 13/2/26(火) 9:57 発言[未読]
【73870】Re:ユーザーフォームで複数条件で検索しシ... 大渕 13/2/26(火) 20:05 お礼[未読]
【73885】Re:ユーザーフォームで複数条件で検索しシ... kanabun 13/2/28(木) 21:03 発言[未読]

【73862】ユーザーフォームで複数条件で検索しシー...
質問  大渕  - 13/2/25(月) 23:53 -

引用なし
パスワード
   検索  A   B  C   D  E    F  G …
 1  No.  CD 23区 CD 町名  丁目 番地 …
 2   1   3  足立  3  綾瀬  1   6
 3   2   4   港   1   芝   1   3
 4   3   4   港   6  台場  3   16
 5   4   3  足立  3  綾瀬  2   1

現在、2500件くらいのデータが入っていて、今後も増えていきます。
抽出する際オートフィルタを使って区や町名、番地と絞っていって
データの変更等行ってますが、データ量が増えていくと詳細に絞り込まないと
必要なデータだけを抽出することが出来ません。

検索ボタンを作り、ユーザーフォームを呼び出しテキストボックス4つ配置して
B列、D列、F列、G列に数字を入力して検索すると絞りこんで表示できるようにしたいです。フィルタかけた状態で集計行も入れられたら入れたいです。

ユーザーフォームのレイアウトまでは作れたんですが、検索のマクロが組めません。
大変恐れ入りますがご教授願います。

【73863】Re:ユーザーフォームで複数条件で検索し...
発言  kanabun  - 13/2/26(火) 9:57 -

引用なし
パスワード
   ▼大渕 さん:

> B列、D列、F列、G列に数字を入力して検索すると絞りこんで表示できる

回答ではありません。

Autofilterでも ▼をクリックするとその列の重複しないアイテムのドロップ
ダウンリストが現れます。ですから Excelがすばやくやってくれること以上の
利便性がないと、わざわざ UserFormで検索値を指定させる意味がないと思います。

たとえば その簡単なサンプル表を例にとれば、
B列のアイテムは 3 か 4 のどちらかです。
いま仮にB列のドロップダウンから 3 を選択したとします。
すると、B列が3のときの D列は必ず 3 で(選択肢は 3しかありえません)
F列の選択肢は 1 か 2 のどちらかです。
F列で 1を選べば G列は 必ず6、F列で 2を選べば G列は必ず 1。
と下位の選択肢は自動的に絞られてきます。

2500件くらいデータがあっても、
B列の重複しないアイテムリストをComboBox1に表示し、どれかのアイテムが
選択されたら、その選択値によって、D列の選択できるアイテムは絞られて
きますので、それを動的に絞り込みComboBox2 にリストするように、
以下同様にして、ComboBox4 までを絞り込み出力させるようなシステムを
(UserForm_Initializeで)つくるようにしておけば、
手動でAutoFilterするより UserFormを使いたくなるとは思います。

【73870】Re:ユーザーフォームで複数条件で検索し...
お礼  大渕  - 13/2/26(火) 20:05 -

引用なし
パスワード
   ▼kanabun さん:

ご指摘ありがとうございます。
B列に関しては1〜50まで、D列は0〜9まで、F列は0〜19まで、G列は1〜3500まであります。

ドロップダウンリストから探すのも時間かかるので、それぞれのComboBOXに数値を入力して検索出来れば格段に作業が早くなると思っております。

【73885】Re:ユーザーフォームで複数条件で検索し...
発言  kanabun  - 13/2/28(木) 21:03 -

引用なし
パスワード
   ▼大渕 さん:

>B列に関しては1〜50まで、D列は0〜9まで、F列は0〜19まで、G列は1〜3500まであります。
>
>ドロップダウンリストから探すのも時間かかるので、それぞれのComboBOXに数値を入力して検索出来れば格段に作業が早くなると思っております。


> ユーザーフォームのレイアウトまでは作れたんですが、

とのことですが、

[Sheet1]
A   B  C   D  E    F  G …
No.  CD 23区 CD 町名  丁目 番地 …
1   3  足立  3  綾瀬   1   6
2   4   港  1   芝    1   3
3   4   港  6  台場   3   16
4   3  足立  3  綾瀬   2   1

よろしければ、
▼もう一つユーザーフォームを挿入し、
 ラベルと【ListBox】を4つ、以下のようなレイアウトで配置して
 テストしてもらえますか?


   CD(B)   CD(D)     丁目      番地
┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐
│     │ │     │ │     │ │     │
│     │ │     │ │     │ │     │
│     │ │     │ │     │ │     │
│     │ │     │ │     │ │     │
│     │ │     │ │     │ │     │
│     │ │     │ │     │ │     │
└─────┘ └─────┘ └─────┘ └─────┘


コードのほうは Dictionaryオブジェクトを使いますので、
まず VBEメニュ−[参照設定] より
★Microsoft Scripting Runtime への参照設定をしておいてください。

以下のコードをUserFormに貼り付けます。
'--------------------------------------------------
Option Explicit
Dim dic As Dictionary

Private Sub UserForm_Initialize()
  Set dic = New Dictionary
  Dim v
  Dim i&
  Dim s1$, s2$, s3$, s4$
  v = Worksheets(1).Range("A1").CurrentRegion.Resize(, 7).Value
  For i = 2 To UBound(v)
    s1 = v(i, 2) 'B列
    s2 = v(i, 4) 'D列
    s3 = v(i, 6) 'F列
    s4 = v(i, 7) 'G列
    If Not dic.Exists(s1) Then
      Set dic(s1) = New Dictionary
    End If
    If Not dic(s1).Exists(s2) Then
      Set dic(s1)(s2) = New Dictionary
    End If
    If Not dic(s1)(s2).Exists(s3) Then
      Set dic(s1)(s2)(s3) = New Dictionary
    End If
    If Not dic(s1)(s2)(s3).Exists(s4) Then
      dic(s1)(s2)(s3)(s4) = Empty
    End If
  Next
  ListBox1.List = dic.Keys()
      
End Sub

Private Sub ListBox1_Click()
  ListBox2.List = dic(ListBox1.Value).Keys()
End Sub

Private Sub ListBox2_Click()
  Dim s1$, s2$
  s1 = ListBox1.Value
  s2 = ListBox2.Value
  ListBox3.List = dic(s1)(s2).Keys()
End Sub

Private Sub ListBox3_Click()
  Dim s1$, s2$, s3$
  s1 = ListBox1.Value
  s2 = ListBox2.Value
  s3 = ListBox3.Value
  ListBox4.List = dic(s1)(s2)(s3).Keys()
End Sub

-----
コードは以上です。
実行すると、ListBox1 に B列CD の候補がリストされますので、どれかを
選択してください。
そうすると、ListBox2に ListBox1で選択したCD の D列CD候補がリストされ
ます。
以下同様にして、ListBox2からどれかのItemを選ぶと、ListBox3の選択肢が
表示され、
そこから一つ選ぶと、ListBox4 の選択肢がリストされるようになっています。

ListBoxの下に CommandButtonをおいて、4つの列の検索値(Filter値)が
決定したらこのボタンを押すと、元シートの表にフィルタをかけるコードを
追加してください。

1517 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free