Findステートメントで特定の行番号を取得する

Question

ユーザーフォーム中のテキストボックスに入力した数字を検索してその行番号を返したいと思い以下のコードを書いてみました。
Private Sub CommandButton1_Click()

Dim Ylin As Long
Dim No As Long

No = TextBox1.Text
Yline = Worksheets("Sheet2").Range("B").Find("No",LookAt:=lWhole).Rows

With Worksheets("ひな型")
    .Cells(11, "D").Value = ComboBox1.Value
    .Cells(16, "D").Value = ComboBox2.Value
    .Cells(27, "F").Value = Cells("Yline", 5).Value
End With

Me.Hide

End Sub

すると実行時エラー380となり、どうもRowsourseプロパティを設定できないとの事。さまざまなサイトを検索してみましたがFindステートメント+Rowで行番号を取得している例はいくつも見受けられるためどうにも納得いきません。

ご指摘宜しくお願いします。

Wendy02 · Accepted Answer

こんばんは。

割り込み失礼します。
今、私が書く時点で、#4のimogasiさんのレスが付いていないようですが、imogasiさんがご指摘のことを別の見方をすれば、
また、#4のimogasiさんのご指摘の、'Rows' ですが、Rowsで値が取れないわけではないけれども、それは、見つけたセルの行数ではなく、行の配列です。#1の補足で直したようですが……。

>Dim No As Long

Variant 型にしておいたほうがよいでしょうね。全体的な問題とは関係ないけれども、指摘していることは正しいと思います。Valで数値化するのは悪くないのですが、Find メソッドを使っているので、そのまま、検索値に入れてしまいます。

全体的にみると、入力値のチェックがされていないので、初歩的な問題がクリアされていないように思います。

Private Sub CommandButton1_Click()
　　Dim Yline As Long
　　Dim No As Variant
　　Dim c As Range
　　Dim sh2 As Worksheet
　　'便宜的にsh2の変数に入れたけれども、単に見やすくするため
　　Set sh2 = Worksheets("Sheet2")
　　'コンボボックスのどちらかに入れていない場合は、離脱
　　If ComboBox1.Value = "" Or ComboBox2.Value = "" Then Exit Sub
　　No = TextBox1.Text
　　'テキストボックスに値が入っていた場合
　　If No <> "" Then
　　　　'Find メソッドの最低のプロパティは入れる。SearchOrder は特にいらない
　　　　Set c = sh2.Range("B:B").Find( _
　　　　What:=No, _
　　　　LookIn:=xlValues, _
　　　　LookAt:=xlWhole, _
　　　　SearchOrder:=xlByRows)
　　　　'見つかった場合にのみ、値を入れる
　　　　If Not c Is Nothing Then
　　　　　　Yline = c.Row
　　　　　　With Worksheets("ひな型")
　　　　　　　　.Cells(11, 4).Value = ComboBox1.Value
　　　　　　　　.Cells(16, 4).Value = ComboBox2.Value
　　　　　　　　.Cells(27, 6).Value = sh2.Cells(Yline, 5).Value
　　　　　　End With
　　　　　　'UserForm は、Hide よりも、Unload のほうが安全
　　　　　　Unload Me
　　　　Else
　　　　　 MsgBox No & " は見つかりません。", 48
　　　　End If
　　End If
　　Set sh2 = Nothing
End Sub


なお、
>ComboBox1.ColumnCount = 1
>ComboBox1.RowSource = "sheet3!A:A"
>ComboBox2.ColumnCount = 1
>ComboBox2.RowSource = "sheet4!A:A"

出来る限り、RowSource は、空白値が含まれる可能性があるので、実害がないものの、実体のある範囲にしてください。

>Findステートメント以外に検索タイプの行番号を返すものがあるといいのですが・・・。
検索は、1列なので、ワークシートのMatch 関数を使いますが、VBAでは、戻り値の変数の方が分からないレベルでは使いこなせないです。受ける変数は、Variant 型にして、Excel 97 書式の、Application.Match とします。しかし、まず、Find が使えるようになってからのほうがよいです。

fujillin · Answer

通りがかりで、横からですが・・・

＞実行時エラー9　インデックスが有効範囲内にありませんと出ました
＞sheet1からsheet4まで存在しています。
＞ひな型というのはshee1の名前です。

などの補足から気になったのは、シート名とシートのオブジェクト名を混同していないだろうかということ。
Sheet2の名前はSheet2でしょうか？
それとも「ひな型」のように違う名前がついてたりしませんか？

　Worksheets("Sheet2").Range("B:B").Find("No",LookAt:=xlWhole).Row
だと、「Sheet2」という名前のシートを指定しています。
ひな型をSheet1と識別するように、オブジェクト名で表記するなら

　　Sheet2.Range("B:B").Find("No",LookAt:=xlWhole).Row
になりますが、こちらの表記法は紛らわしいので、あまり使用する人はいないのではないでしょうか。   この他にも

　　　Worksheets(2).Range("B:B").Find("No",LookAt:=xlWhole).Row
あるいは xを変数として
　　　Worksheets(x).Range("B:B").Find("No",LookAt:=xlWhole).Row
などのような指定方法もありますが、それぞれ意味が変わります。
その他の指摘は、それぞれの回答ででていますので・・・

こちらの勘違いでしたら、無視してください。

xls88 · Answer

>実行時エラー9　インデックスが有効範囲内にありません
Yline = Worksheets("Sheet2").Range("B:B").Find(No, LookAt:=xlWhole).Row
で上記エラーになるなら、Worksheets("Sheet2")しかないと思います。

>sheet1からsheet4まで存在しています。
ということですが、
With Worksheets("ひな型")
で登場する「ひな型」という名前のシートは存在しないのですか？
それとも「ひな型」という名前のシートは他のブックにあるのですか？

もし他のブック、ということなら
Workbooks("ブック名.xls").Worksheets("Sheet2")
というように、ブック名で修飾してみてください。

imogasi · Answer

>Range("B"). はRange("B：B").
＞Find("No",LookAt:=lWhole).Rows　はFind(No,LookAt:=lWhole).Rows
でしょう。
 Worksheets("Sheet2").Range(
は Worksheets("Sheet2").を前もってActivateしておいたほうが良さそう。
NoはテキストボックスのTEXTには数字文字列ではいりますが、セルは数値ですか。合わせておいたほうがよいと思う。
＞LookAt:=lWhole).Rows
は.Rowの方が良いのでは。
＞Rowsourseプロパティを設定できないとの
質問実例には載っていないが。突然ですが。
ーー
どこまで役立つかわかりませんが参考になれば。

xls88 · Answer

対象ブックに、Sheet2 は存在していますか？

xls88 · Answer

エラーになる行はここですか？
.Cells(27, "F").Value = Cells("Yline", 5).Value
Cellsプロパティの、行インデックスが文字列になっています。
Excelに、そのような行はありません。

xls88 · Answer

B列が対象なら
Worksheets("Sheet2").Range("B")
は
Worksheets("Sheet2").Range("B:B")
ではないでしょうか。

折角の変数が文字列になっています。
No = TextBox1.Text
Worksheets("Sheet2").Range("B:B").Find("No", LookAt:=lWhole).Rows
は、ダブルクオーテーションで挟んではいけません。
No = TextBox1.Text
Worksheets("Sheet2").Range("B:B").Find(No, LookAt:=lWhole).Rows
です。

スペルミス（？）が２ヶ所あります。
Worksheets("Sheet2").Range("B:B").Find(No, LookAt:=lWhole).Rows
は、
Worksheets("Sheet2").Range("B:B").Find(No, LookAt:=xlWhole).Row
lWholeはxが欠落、Rowsはsが余分

Findステートメントで特定の行番号を取得する

こんばんは。

通りがかりで、横からですが・・・

>実行時エラー9 インデックスが有効範囲内にありません

この回答への補足

>Range("B"). はRange("B：B").

対象ブックに、Sheet2 は存在していますか？

この回答への補足

エラーになる行はここですか？

この回答への補足

B列が対象なら

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

>実行時エラー9　インデックスが有効範囲内にありません