[[20190109113221]]　『セルの範囲より特定文字を全てプルダウンリストに』（PAZU）　ページの最後に飛ぶ

『セルの範囲より特定文字を全てプルダウンリストに表示したい』（PAZU）

みなさん宜しくお願いします。

EXCEL2013を使用しています。

Ａ列（名前）　　Ｂ列（入力値）　　Ｃ列
あいだ　　　　　な　　　　　　　　プルダウンで該当の名前を表示
きなし
しまだ
すずき
たなか

上記のようなＥＸＣＥＬでＡ列に名前がありＢ列に「な」と入力したとき、
Ｃ列に入力規制のリストで作成したプルダウンに表示したいです。
この場合、プルダウンに表示したい名前は、「きなし」「たなか」が
表示されたいです。

拙い文章ですがどうぞよろしくお願いします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

	A	B	C	D	E
1	名前	検索	入力規則		2
2	あいだ	な	      ▼		きなし
3	きなし				たなか
4	しまだ				
5	すずき				
6	たなか				
7					
8					
9					
10					
11					
12					
13					
14					
15					
16					
17					
18					
19					
20

 E列作業列

 E1 =COUNTIF(E2:E20,"*?")

 E2 =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$20)/(FIND(B$2,A$2:A$6)>0),ROW(A1))),"")
 E20まで下へコピー。

 C3 入力規則 「リスト」

 範囲を =INDIRECT("E2:E"&E1+1) に設定

 ※A2～A20の範囲としてま。
（GobGob） 2019/01/09(水) 12:03

 マクロでやるなら
 シートモジュールに

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Data As String
    Dim i As Long
    If Target.Address(0, 0) = "B2" Then
        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            If InStr(Range("A" & i).Value, Target.Value) > 0 Then
                Data = Data & Range("A" & i).Value & ","
            End If
        Next i
        With Range("C2").Validation
            .Delete
            .Add xlValidateList, , , Data
        End With
    End If
 End Sub
（ろっくん） 2019/01/09(水) 12:57

検索文字を入力するのは、B2セルだけで良いのでしょうか？ B3セル以降にも入力していくと、それぞれの行のC列が変わっていく、とかだったりは？
（???） 2019/01/09(水) 13:13

GobGobさん、ろっくんさん

どちらもうまくできました。
すごく助かりましたし勉強になりました。

お忙しい中にもかかわらず本当にありがとうございました。

???さん

今回は、B3以降の入力は無いのですが、もし以降の入力があった場合
どのようなコーディングになるのでしょうか？
（PAZU） 2019/01/09(水) 13:27

ろっくんさん

存在しない文字を入力した場合、
下記構文でエラーが発生するのですがどう対応すればよろしいでしょうか？
よろしくお願いします。

           .Add xlValidateList, , , Data

（PAZU） 2019/01/09(水) 13:37

たとえば、 If Data <> "" Then のときだけ Add するように変えれば、不正入力対応できるのでは？

そして、B3セル以下の入力にも対応するならば、"B2"とか"C2"とか決め打ちしている箇所を、TargetのRowを元にするよう書き換えれば良いかと思いますよ。応用はご自身で考えてください。
（???） 2019/01/09(水) 13:59

？？？さん

エラーの件、ありがとうございました。

B3以降についてもあとでやってみます。
本当にありがとうございました。

（PAZU） 2019/01/09(水) 14:45

 解決済みかもですが、エラーの件は
 下記のようにしてもいいですよ。

 Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Data As String
    Dim i As Long
    Data = ","
    If Target.Address(0, 0) = "B2" Then
        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            If InStr(Range("A" & i).Value, Target.Value) > 0 Then
                Data = Data & Range("A" & i).Value & ","
            End If
        Next i
        With Range("C2").Validation
            .Delete
            .Add xlValidateList, , , Data
        End With
    End If
 End Sub
（ろっくん） 2019/01/09(水) 15:41

コメント返信：

[ 一覧(最新更新順) ]

[[20190109113221]] 『セルの範囲より特定文字を全てプルダウンリストに』（PAZU） ページの最後に飛ぶ

[[20190109113221]]　『セルの範囲より特定文字を全てプルダウンリストに』（PAZU）　ページの最後に飛ぶ