VBA 配列で型がエラーになります。

いつもお世話になってます。

入力フォームのTextBox1（＝By)　に文字を入力し検索ボタンを押すと
「data」シートのE列から　あいまい一致　した行の中から、指定の4列がリストボックス
に表示されるようにしたいです。

E列以外は数値のため、数字で検索すると思い通りのデータが抽出されます。
が、E列の文字検索だと「13　型が一致しません」となります。
配列がわからず、どこを直せばよいのか四苦八苦しております。
よろしくお願いします。

Private Sub CommandButton1_Click()

Dim lastRow As Long
Dim myData, myData2(), myno
Dim i As Long, j As Long, cn As Long
Dim By As String

With Worksheets("data")
lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
myData = .Range(.Cells(1, 1), .Cells(lastRow, 8)).Value
By = UserForm3.TextBox1.Value
End With

ReDim myData2(1 To lastRow, 1 To 4)
For i = LBound(myData) To UBound(myData)
If myData(i, 5) Like "*" & By & "*" Then　←ここがエラーになります。
cn = cn + 1
myData2(cn, 1) = myData(i, 2)
myData2(cn, 2) = myData(i, 3)
myData2(cn, 3) = myData(i, 5)
myData2(cn, 4) = myData(i, 8)

End If
Next i

With ListBox1
.ColumnCount = 4
.ColumnWidths = "20;60;80;100"
.list = myData2
End With

End Sub

No.11 ベストアンサー 回答者： Qchan1962 回答日時： 2020/03/03 18:17

なるほど、解決ではないにしても原因のデータを作り直す事なども出来そうですね

別ファイルから転記した後、該当列セルを調べて変換しておくなどすれば、ご質問のコードで良い事になります。
型　13エラーに付いて調べれば、解決策が講じられると思います。

参考：https://vbabeginner.net/vba%E3%81%AEempty%E3%82% …

No.10 回答者： Qchan1962 回答日時： 2020/03/03 13:43

＃９に付いて

＞Sub または Functiion　定義がありません　となりました。

単体でプロシージャに書き込んだのですか？
ご質問にあるコードの下記部分
For i = LBound(myData) To UBound(myData)
If myData(i, 5) Like "*" & By & "*" Then　←ここがエラーになります。
cn = cn + 1
myData2(cn, 1) = myData(i, 2)
myData2(cn, 2) = myData(i, 3)
myData2(cn, 3) = myData(i, 5)
myData2(cn, 4) = myData(i, 8)
End If
Next i
を下記に置き換えて（書き直して）見てください。
　　For i = 1 To UBound(myData)
　　　　If IsNull(myData(i, 5)) = False Then　’Nullを飛ばす（Trueなら処理しない）
　　　　　　If myData(i, 5) Like "*" & By & "*" Then　　'←ここがエラーになります｡？
　　　　　　　　cn = cn + 1
　　　　　　　　myData2(cn, 1) = myData(i, 2)
　　　　　　　　myData2(cn, 2) = myData(i, 3)
　　　　　　　　myData2(cn, 3) = myData(i, 5)
　　　　　　　　myData2(cn, 4) = myData(i, 8)
　　　　　　End If
　　　　End If
　　Next i

また、セルデータを手入力した場合は、どうでしょう。（検証の為）


参考に　デモ用に作ったものを少し手を加えた検証用コード（ただし、セルデータは、手入力）
コメント部は不要（検証用）
Private Sub CommandButton1_Click()
　　Dim TestVariant As Variant　’検証用
　　Dim lastRow As Long
　　Dim myData As Variant, myData2 As Variant　　'省略型を明示　ReDimで（）
　　Dim i As Long, j As Long, cn As Long
　　Dim FindKey As String
　　FindKey = Me.TextBox1.Value　'同じUserFormにあるTextBox1に変更
　　With Worksheets("data")
　　　　lastRow = .Cells(Rows.Count, 5).End(xlUp).Row　　'条件列　E列に変更
　　　　myData = .Cells(1, 1).Resize(lastRow, 8) '配列にセル範囲を設定
　　　　'　Test　myData配列に範囲でなく1つずつ値で入力
　　　　'　　　　ReDim myData(1 To lastRow, 1 To 8)
　　　　'　　　　For j = 1 To 8
　　　　'　　　　　　For i = 1 To lastRow
　　　　'　　　　　　　　myData(i, j) = .Cells(i, j)
　　　　'　　　　　　Next
　　　　'　　　　Next
　　End With
　　ReDim myData2(1 To lastRow, 1 To 4)
　　For i = 1 To UBound(myData)
　　　　If IsNull(myData(i, 5)) = False Then　　　　　'コピー元データが不明の為Null値を除外
　　　　　　If myData(i, 5) Like "*" & FindKey & "*" Then　　'←ここがエラー？
　　　　　　　　cn = cn + 1
　　　　　　　　myData2(cn, 1) = myData(i, 2)
　　　　　　　　myData2(cn, 2) = myData(i, 3)
　　　　　　　　myData2(cn, 3) = myData(i, 5)
　　　　　　　　myData2(cn, 4) = myData(i, 8)
　　　　　　End If
　　　　End If
　　　　'TestVariant = Worksheets("data").Cells(i, 5)
　　　　'Debug.Print TestVariant; " : "; TypeName(TestVariant); " : "; IsNull(TestVariant)
　　Next i
　　With Me.ListBox1
　　　　.ColumnCount = 4
　　　　.ColumnWidths = "20;60;80;100"
　　　　.List = myData2
　　End With
End Sub

この回答へのお礼

お世話になってます。
いろいろとやってみたところ、4列目と6列目に文字を手入力したところ、
どちらも文字検索に成功しました。
4列目、6列目はそれぞれUserFormのテキストBOXの値です。が、
5列目は、下記の通りあとから別フォルダを参照した結果を代入しています。

Cells(lastRow, 6).FormulaR1C1 = "=INDEX([担当一覧.xlsx]担当ｺｰﾄﾞ!C5,MATCH(RC[-2],[担当一覧.xlsx]担当ｺｰﾄﾞ!C4,0))"
Cells(lastRow, 6).Value = Cells(lastRow, 6).Value

これが問題のような気がしてきました。担当一覧は3000件ほどですが、名前が紛らわしいためコードで管理しています。
現在リストボックスには全データが表示されるため
検索を相談させていただきましたが、幸い１日50～60件のデータですので
スクロールで探すこともできそうです。

まだまだお尋ねしたいことがありますので、機会がありましたら今後ともご教示いただけると助かります。
ありがとうございました。

お礼日時：2020/03/03 17:23

No.9 回答者： Qchan1962 回答日時： 2020/03/02 17:13

返信をいただいても、今日対応できるか分からないので、

手元の環境を再現できず、試しになりますが、、

　　For i = 1 To UBound(myData)
　　　　If IsNull(myData(i, 5)) = False Then　’Nullを飛ばす（Trueなら処理しない）
　　　　　　If myData(i, 5) Like "*" & By & "*" Then　　'←ここがエラーになります｡？
　　　　　　　　cn = cn + 1
　　　　　　　　myData2(cn, 1) = myData(i, 2)
　　　　　　　　myData2(cn, 2) = myData(i, 3)
　　　　　　　　myData2(cn, 3) = myData(i, 5)
　　　　　　　　myData2(cn, 4) = myData(i, 8)
　　　　　　End If
　　　　End If
　　Next i

この回答へのお礼

お忙しいところお手数おかけしています。
イミディエイトウィンドも初めて使いました。勉強になります。
結果は下記の通りでした。

田中：String
田中：String
田中：String
:Empty
:Empty
:Empty

’Nullを飛ばす　は　myData2(cn,1)=myData(i,2) ←ここで
Sub または Functiion　定義がありません　となりました。
回避方法がわからず、ご面倒おかけします。

お礼日時：2020/03/03 12:03

No.8 回答者： Qchan1962 回答日時： 2020/03/02 16:54

＃７のコード下記に変えてください。

ループの終了数　10は、任意入力でお願いします。
Sub Test()
　Dim i As Long
　Dim myVar As Variant
　　For i = 1 To 10
　　　myVar = Worksheets("data").Cells(i, 5)
　　Debug.Print myVar; " : "; TypeName(myVar)
　　Next
End Sub

No.7 回答者： Qchan1962 回答日時： 2020/03/02 16:49

コード自体は、myDataはセル範囲なので、LBound(myData)を1で良いとか、

なんとなく個人的にこっちの方が理解し易いとか、myData = .Cells(1, 1).Resize(lastRow, 8)
Byの変数名がなんとなく気になるとか、
By = Me.TextBox1.Valueは、Withの外に出してもいいんではないか、とかありますが、
いずれも13エラーを発生する要因ではないと思います。（消してダメ出しではありません。）

Nullがあり上手く変換できていないとか、、データ側を見てみたいので下記を標準モジュールにコピペして
イミディエイトウィンドウを確認してみてください。
対象のE列の内容も教えて頂ければと思います。

Sub Test()　’E列のセルの値のTypeを調べる
　　Dim lastRow As Long, i As Long
　　Dim myVar As Variant　
　　lastRow = Worksheets("data").Cells(Rows.Count, 5).End(xlUp).Row
　　For i = 1 To lastRow
　　　　myVar = Worksheets("data").Cells(i, 5)
　　　　Debug.Print TypeName(myVar)
　　Next
End Sub

手数を掛けますが、、

No.6 回答者： Qchan1962 回答日時： 2020/03/02 14:07

＞コマンドボタンで別ファイルのコード表からから担当者名を検索

Worksheets("data")は、別ブックと言う事でしょうか？
であれば、その別ブックが開いているなら、With Worksheets("data")は、
With Workbooks("ブック名.xlsx").Worksheets("data")　のようにしなくては、
が、　dataシートが無ければ実行時エラー9だと思いますので、違うか。。
エラーの上に　Debug.Print　を差し込んでイミディエイトウィンドウで確認してみては？
For i = LBound(myData) To UBound(myData)
Debug.Print By; " : " & myData(i, 5)
If myData(i, 5) Like "*" & By & "*" Then '←ここがエラーになります｡？

結果をお知らせください。

この回答へのお礼

お付き合いいただき恐縮です。

別ブックからデータを検索した結果は、
”data"シートに.VALUE で正しく反映されています。（E列ピンク）
念のため、使用していない15列目に担当者を転記し、
If myData(i, 15) Like "*" & By & "*" Then にしてみましたが、
結果は同じく13 型エラーです。

5列目「中」で検索をしたところ
イミディエイトウィンドウに下記のように出てきました。中は全部で10行ありました。
これがどうマズイのか、わからないのが残念ですが。
--------------
中：担当者
中：
中：
中：
中：

お礼日時：2020/03/02 15:10

No.5 回答者： Qchan1962 回答日時： 2020/03/02 11:42

>ローカルウィンドで変数を確認しましたが、By が式に表示されません。

これがマズイのでしょうか。。。
ううん。
＞E列以外は数値のため、数字で検索すると思い通りのデータが抽出されます。
なので下記は関係ないと思いますが、
このコードは、UserForm3に書かれているのでしょうか？
画像だとタイトルはUserForm3になっていますが、オブジェクト名は？
各オブジェクト名を確認してみてください。

検証したコードです。
いじるまでもエラーは出ませんでしたが、少し変更しました。

Option Explicit
Private Sub CommandButton1_Click()
　　Dim lastRow As Long
　　Dim myData As Variant, myData2() As Variant　'省略型を明示
　　Dim i As Long, j As Long, cn As Long
　　Dim By As String
　　With Worksheets("data")
　　　　lastRow = .Cells(Rows.Count, 5).End(xlUp).Row '条件列　E列に変更
　　　　myData = .Range(.Cells(1, 1), .Cells(lastRow, 8)).Value
　　　　By = Me.TextBox1.Value　'同じUserFormにあるTextBox1に変更
　　End With
　　ReDim myData2(1 To lastRow, 1 To 4)
　　For i = LBound(myData) To UBound(myData)
　　　　If myData(i, 5) Like "*" & By & "*" Then　　'←ここがエラーになります｡？
　　　　　　cn = cn + 1
　　　　　　myData2(cn, 1) = myData(i, 2)
　　　　　　myData2(cn, 2) = myData(i, 3)
　　　　　　myData2(cn, 3) = myData(i, 5)
　　　　　　myData2(cn, 4) = myData(i, 8)
　　　　End If
　　Next i
　　With ListBox1
　　　　.ColumnCount = 4
　　　　.ColumnWidths = "20;60;80;100"
　　　　.List = myData2
　　End With
End Sub

この回答へのお礼

変更箇所を教えていただいた通りに直しましたが、結果は変わりませんでした；；
フォームからの新規登録時に
「担当者コードを入力すると、コマンドボタンで別ファイルのコード表からから担当者名を検索し
担当者欄に名前が入ります」
↑これが文字と認識しない？なんてことでしょうか。。。

お礼日時：2020/03/02 13:05

No.4 回答者： Qchan1962 回答日時： 2020/02/28 20:50

良く表を見ると、C列の日付は、ユーザー定義のm/dを左寄せにしているのでしょか？それとも文字？

出力を見ると2020/・・なので　m/dですね。再検証で、文字とm/dにしてしましたが、このあたりでも問題ありませんでした。
使われていない　myno　が気になりますが、、、なんででしょう？（Excel2013)

No.3 回答者： くんこば 回答日時： 2020/02/28 20:23

配列も横着せず、ちゃんと型宣言しましょう。

もしくは比較する時にキャストしてやる。

それから、配列の要素数が決まってるので、boundは要らない。まぁあっても動くけど。

この回答へのお礼

ご指摘ありがとうございます。
エラーの原因がわからないのは勉強不足なのがわかりました。
抽出はあきらめます。

お礼日時：2020/03/02 10:42

No.2 回答者： Qchan1962 回答日時： 2020/02/28 20:22

＃１の結果