(VBA)検索リストを表示しながら検索をするユーザーフォームの作成

Question

●やりたいこと
顧客管理データベースがあり、その更新用に
以下のようなユーザーフォームを作成しました。
右側黄色枠内にデータベースにある顧客情報が一覧で記載され、
その中の情報を選択すると左側の情報が入り、変更できるといったフォームにしたいです。

●データベース
以下のような項目で、37列目まで続いています。
検索するのは、10列目の団体名で、「株式会社テラテイル」のように
漢字、カタカナ、ひらがな、英語が混在しています。

●困っていること
黄色枠内に顧客情報を表示させることはできましたが、
「検索画面」の下の白枠内に顧客名を入れてもエラーが出てしまいます。
例えば、データベースに「株式会社テラテイル」という情報があったとして、
検索画面に「てら」といれて検索ボタンを押すと「株式会社テラテイル」という情報が
黄色枠内に表示されるようにしたいです。

●エラーの内容と作成中のコード
データベースのシートの内容を別シート（作業）にコピーし、
条件にあった情報のみを残していくイメージです。
ただ、やりたいことが実現できれば全く違うコードでも問題ありません。
エラーとなる部分を太字に、⇒でエラー内容を記載しています。

Dim data, str As String, strz As String, i As Long, tori As String, furi As String
str = txt検索.Text

str = StrConv(str, vbKatakana) 'カタカナ
str = StrConv(str, vbUpperCase) '大文字
str = StrConv(str, vbWide) '全角

With Sheets("データベース")
If .autfiltermode Then
.Range("A1").autfilter
End If

data = Range("A1").CurrentRegion
End With

Application.ScreenUpdating = False

With Sheets("作業用")
.Cells.Clear
.Range("A1", .Cells(UBound(data), UBound(data, 2))) = data

With .Range("A1").CurrentRegion.Sort_
key1:=.Offset(,10),order1:=xlascending, header:=xlyes,_
key2:=.offset(,11),order3:=xlascending
⇒「構文エラー」と出てしまいます。11列目には先方の担当者名が記載されています。

End With

'余分な行の削除
For i = UBound(data) To 2 Step -1

tori = StrConv(.Cells(i, 10), vbKatakana)
tori = StrConv(tori, vbUpperCase)
tori = StrConv(tori, vbWide)
⇒エラー：参照が不正または不完全です。

furi = StrConv(.Cells(i, 11), vbKatakana)
furi = StrConv(tori, vbUpperCase)
furi = StrConv(tori, vbWide)

If (InStr(tori, str) > 0 Or InStr(furi, str) > 0) = False Then
.Cells(i, 1).EntireRow.Delete
End If

Next i

Erase data
data = .Range("A1").CurrentRegion

End With

With ListBox1
.ColumnCount = -1
.ColumnWidths = "0;0;40;30;60;60;0;0;0;200;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0"
.List = data
End With

Application.ScreenUpdating = True

End Sub

以上となります。
まだマクロを勉強し始めたばかりで基本的なところができていないかもしれません。。
お知恵を貸していただけませんでしょうか・・

Answer 1

構文エラーの方は既に回答が出ているのでもう一つの方を
と言ってもjinojiさんがコメントで答えを出していますが。

'StrConvのconversionはbit処理してるからOrで繋げれば同時処理できるんです
tori = StrConv(Sheets("データベース").Cells(i, 10).Value, vbKatakana Or vbUpperCase Or vbWide)

Answer 2

とりあえず以下のようにするとエラーは消える。

With .Range("A1").CurrentRegion
    .Sort key1:=.Offset(, 10), order1:=xlAscending, Header:=xlYes, key2:=.Offset(, 11), order3:=xlAscending
End With

Comments

[liliy89]

回答ありがとうございます。構文エラーは表示されなくなりました。もう1つのエラーの方はいかがでしょうか。「参照が不正または不完全です。」と出てしまい、検索ができません。

[jinoji]

おそらくWith と End With の対応がずれているのだと思います。
.Cells(i, 10)は どのWith句の中にあるつもりで書いていますか？

[liliy89]

返信が遅くなりすみません。実は.cells（i,10）のエラーが表示されなくなった代わりに、.Range("A1", .Cells(UBound(data), UBound(data, 2))) = dataで「型が一致しません」と出てしまいます。。variant型に変更しても同じエラーになります。質問しすぎるのも良くないと思って自力で試行錯誤したのですが、うまくいきませんでした。。

[jinoji]

数行上の
data = Range("A1").CurrentRegion
を
data = .Range("A1").CurrentRegion
としたらどうでしょう。

[liliy89]

返信ありがとうございます(；；)エラーは出なくなりましたが、実行するとしばらく固まった後、検索画面には何も表示されません。。参考にしたコードはデータベースにカタカナ表記があるのに対して、私の方はカタカナ、漢字、英字が混ざっているので、並び替えでうまく検索できないのかな・・と思っています。そこで、並び替える方法ではなく、検索する方法を試してみました。（以下のコードです）が、こちらも検索結果が真っ白です。。
前回からデータベースの列番が変わっていて、5列目に担当者名、8列目に組織名があり、例えば検索窓に「田」と打つと、田中さんや田中株式会社など「田」が含まれている行が表示されるイメージです。できれば全部のデータが検索結果（listbox１）が表示されるようにしたいのですが、参考にしたコードのcn+1のところが分からなかったので、一部だけでも表示させたいと手を付けれずにいます。質問ばかりですみません。。

Private Sub CommandButton1_Click()
'検索機能
Dim data, str As String
Dim lastRow As Long
Dim myData, myData2(), myno
Dim i As Long, j As Long, cn As Long
str = txt検索.Text

If str = "" Then
MsgBox "検索ワードを入力して下さい。"
Exit Sub
End If

str = StrConv(str, vbKatakana) 'カタカナ
str = StrConv(str, vbUpperCase) '大文字
str = StrConv(str, vbWide) '全角

'検索するデータを配列　myData　に格納
With Worksheets("データベース")
lastRow = .Cells(Rows.Count, 1).End(xlUp).row
myData = .Range(.Cells(1, 1), .Cells(lastRow, 8)).Value
End With

'配列　myData の中で検索で一致したデータをリストボックスに配置
ReDim myData2(1 To lastRow, 1 To 3)
For i = LBound(myData) To UBound(myData)
If myData(i, 5) Like "*" & txt検索.Value & "*" And myData(i, 8) Like "*" & txt検索.Value & "*" Then
cn = cn + 1
myData2(cn, 1) = myData(i, 3)
myData2(cn, 2) = myData(i, 5)
myData2(cn, 3) = myData(i, 8)
End If
Next i

With ListBox1
.ColumnCount = -1
.ColumnWidths = "30;70;70"
.List = myData2
End With

End Sub

[liliy89]

検索はなんとか引っかかるようになりました。壁はあと2つです。。
①検索をクリックしてlistboxに表示された結果をクリックしても左側（組織名や担当者名）に表示されない。（エラー：listプロパティの値を取得できません。引数が無効です）
②検索結果をlistboxに表示させるときはデータベース全てのデータを表示させたい

[liliy89]

上記2点、自己解決しました。
お助け頂きありがとうございました！