エクセル 検索について

質問

エクセル２００３にて

一つのブックの中に３０シート有ります。
末尾のシートが”検索用”というシートで他が入力用です。

検索用シートのC列に店舗一覧が約３０００件載っています。
全て他プログラムからの引用なので、ある程度は整列されていますが、きれいに並び替えができません…　例）横田屋A支店が”お”のならびにあったり、横田屋B支店が”よ”のならびにあったりです

入力用シートのEが入力列です。
Eの入力セルで”検索用”シートから検索をしています。

現状では
Crtl+Fで検索画面を出し、支店名が不明な場合が多いので”横田屋”までを検索し、ブック内検索のすべてで表示から、希望の支店を選び、データをコピー。入力用シートの入力セルにカーソルを合わせ直し、貼り付け。
という作業を繰り返しています。

×改善案
データ→入力規則→リストでドロップダウンから選択だと並び替えができていない点と、リストが３０００件だと選択しにくいという理由で×にしました。

なにか現状より良い方法はないでしょうか？？？
検索にCtrl+Fを押さなくてよい方法。検索先から入力用カーソルにもっと簡単にコピー貼り付けする方法等です。
よろしくお願いします。

回答No.2

normo · ベストアンサー

こんな感じでいかがでしょう？説明は省いてコードだけ載せます。あまり自信がないのでしっかり動作確認してくださいね。
ただし、店舗一覧の中に「,」の文字列が入っていないことが条件です。

○全ての入力用シートそれぞれのコードに以下をコピー

Private Sub Worksheet_Change(ByVal Target As Range)
　FindTenpo Target
End Sub

○新規の標準モジュールに以下をコピー
開始5行の定数の宣言部分の値を書き換える

Const cstSheetFind As String = "検索用" '← 検索用シートの名前
Const cstRowFrom As Integer = 2 '← 店舗一覧の開始行
Const cstRowTo As Integer = 3000 '← 店舗一覧の最終行
Const cstColFind As Integer = 3 '← 店舗一覧の列
Const cstColInput As Integer = 5 '← 入力用シートの入力列
Public booSin As Boolean

Sub FindTenpo(ranTrg As Range)
　If booSin Or ranTrg.Column <> cstColInput Or ranTrg.Rows.Count > 1 Or ranTrg.Columns.Count > 1 Then
　　Exit Sub
　End If
　Dim shtFind As Worksheet
　Dim intRow As Integer
　Dim strFind As String
　Dim strLst As String
　Dim strTrg As String
　strTrg = ranTrg.Value
　If strTrg = "" Then
　　ranTrg.Validation.Delete
　　Exit Sub
　End If
　On Error Resume Next
　If Not InStr(1, ranTrg.Validation.Formula1, strTrg) > 0 Then
　Else
　　ranTrg.Validation.Delete
　　Exit Sub
　End If
　On Error GoTo 0
　Set shtFind = ThisWorkbook.Worksheets(cstSheetFind)
　strLst = ""
　For intRow = cstRowFrom To cstRowTo
　　strFind = shtFind.Cells(intRow, cstColFind)
　　If InStr(1, strFind, strTrg) > 0 Then
　　　strLst = strLst & "," & strFind
　　End If
　Next
　If strLst = "" Then
　　ranTrg.Select
　　MsgBox "NOT FOUND!!", vbInformation
　Else
　　booSin = True
　　strLst = Mid(strLst, 2)
　　If InStr(1, strLst, ",") = 0 Then
　　　ranTrg.Validation.Delete
　　　ranTrg.Value = strLst
　　Else
　　　With ranTrg.Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, Formula1:=strLst
　　　　.InCellDropdown = True
　　　　.ShowError = False
　　　End With
　　　ranTrg.Select
　　　SendKeys "%{DOWN}"
　　End If
　　booSin = False
　End If
End Sub

お礼

ありがとうございます！！！

理由は不明ですが一部のワード”倉庫”や”大阪”などを除きほぼ完璧に動作しました。
（エラー画面のあとデバックというのを選択すると
.Add Type:=xlValidateList, Formula1:=strLst
の一文が黄色で表示されています。
候補のHIT数が多すぎるのでしょか？ちなみに”倉庫”や”大阪”だけも検索することがあります…）

別件で、もうひとつお聞きしたいのですが、検索タブをクリックすると候補一覧が出てくるんですが入力セルの幅が狭いのと（規定のものなので変更不可）、文字が小さいのでちょっと検索しにくいのです……

見やすいように変更可能でしょうか？？？

回答No.3

normo

入力規則のリストを
「店舗1,店舗2,店舗3,・・・」と文字列で指定している為、それが255文字を超えるとエラーになるらしいです。おそらくこれが原因かと思われます。

そこで２つ提案です。
1つ目：スペースで区切って「大阪　北」の様に複数の文字列を指定可能にしてさらに絞り込めるようにするという方法です。入力作業としては実用的にどうですか？
2つ目：作業用のシートを用意、または既存のシートの余白部分へリストを出力しセル範囲を指定する。これなら255文字超える事はありません。
どっちの方が良さそうですか？または両方を適用するか？
もっと他によい方法を思いつけばいいのですが・・・

残念ながら入力規則のリストの文字の大きさ等は変更できないのです。古典的な方法ですが、ズームで表示倍率を変えて大きくしてみると少しは見やすくなるかと思います。

回答No.1

normo

マクロは使わずに処理したいのでしょうか？

お礼

マクロOKです。

どんな方法でも良いので作業を簡単にしたいです。

検索して希望の支店名を選択というのは手動で、あとの処理が自動なら最高です。