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

質問

ユーザーフォーム中のテキストボックスに入力した数字を検索してその行番号を返したいと思い以下のコードを書いてみました。
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で行番号を取得している例はいくつも見受けられるためどうにも納得いきません。

ご指摘宜しくお願いします。

回答No.6

Wendy02 · ベストアンサー

こんばんは。

割り込み失礼します。
今、私が書く時点で、#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 が使えるようになってからのほうがよいです。

お礼

ほぼ、解決できました。有難うございました。

初歩的な問題がクリアされていない＞ご指摘のとおり1ヶ月程度の期間参考書やネットに落ちているソースコードを見ながら独学で試行錯誤しているだけなので基本を通過しているわけではありません。すぐに横着してしまうタイプなので、必要だとある程度分かっていても皆さんのように宣言をしっかりして全体の構造をイメージしてからというような作業を省いてしまっているところがありますが今回のことでその辺から反省してみようと思います。

戻り値、ApplicationMatchなど今後勉強する為にいろいろとキーワードまで提供していただき有難うございました。

回答No.7

fujillin

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

＞実行時エラー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
などのような指定方法もありますが、それぞれ意味が変わります。
その他の指摘は、それぞれの回答ででていますので・・・

こちらの勘違いでしたら、無視してください。

お礼

ご指摘有難うございます。

ご指摘のとおりShhet2にも名前がついております。No.6さんの御礼にも書いたようにVBAに取り組んで1ヶ月そこそこです。まだ、オブジェクトやクラスなどはあやふやなところが多いです。

No.6さんに提供していただいたコードでも同様のエラーが直りませんでしたが、Setステートメントの部分のシート名をシートのラベル名にしてみたところすんなり走りました。(最初に自分で書いたものでは結局だめでしたが)

結局、何が原因だったのか個人的にはあやふやですが、参照先の表記を変えることで解決できたのならおそらくNo.7さんのご指摘の内容がもっとも関係がありそうなことなのでそこら辺をさらに勉強してみようと思います。

回答No.5

xls88

>実行時エラー9　インデックスが有効範囲内にありません
Yline = Worksheets("Sheet2").Range("B:B").Find(No, LookAt:=xlWhole).Row
で上記エラーになるなら、Worksheets("Sheet2")しかないと思います。

>sheet1からsheet4まで存在しています。
ということですが、
With Worksheets("ひな型")
で登場する「ひな型」という名前のシートは存在しないのですか？
それとも「ひな型」という名前のシートは他のブックにあるのですか？

もし他のブック、ということなら
Workbooks("ブック名.xls").Worksheets("Sheet2")
というように、ブック名で修飾してみてください。

補足

ひな型というのはshee1の名前です。
ご指摘どおりworkbooks等できる限り参照先を詳細にしてみましたが効果がありません。(同じエラーです。)
ちなみに完全一致など、変えられる条件は変えたりsheet2の検索列に入れている数字の書式などもチェックしてみました。
他に提示していない情報としては、フォームを開いたときのInitializeについてです。

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False

ComboBox1.ColumnCount = 1
ComboBox1.RowSource = "sheet3!A:A"
ComboBox2.ColumnCount = 1
ComboBox2.RowSource = "sheet4!A:A"
Application.ScreenUpdating = True
Worksheets("ひな型").Activate

End Sub
このようにしているのでTextbox1にたいしてどうこうというのは無いと思うのですが、これ以上は(実はもうこれで2日ほど費やしています)ちょっとつめられないのでアドバイスがいただけないときは別な検索方法を考えてみようと思います。

Findステートメント以外に検索タイプの行番号を返すものがあるといいのですが・・・。

回答No.4

imogasi

>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プロパティを設定できないとの
質問実例には載っていないが。突然ですが。
ーー
どこまで役立つかわかりませんが参考になれば。

回答No.3

xls88

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

補足

さすがにその点は心配ありません。
sheet1からsheet4まで存在しています。
またそれぞれのシートはすでにデータを書き込んで表にしてあり、あとは検索マクロを作製しさえすれば目標の状態に持っていくことができるようにしてあります。

回答No.2

xls88

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

補足

エラー行を提示していませんでした。すみません。

エラー構文は
Yline = Worksheets("Sheet2").Range("B:B").Find(No, LookAt:=xlWhole).Row

の部分です。
ご指摘の箇所も訂正します。

回答No.1

xls88

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が余分

補足

早々に回答していただきありがとうございます。スペルミスにつきましてはお恥ずかしい限りです。
ご指摘の部分を直してみました。フォームに入力してコマンドボタンをおすと実行時エラー9　インデックスが有効範囲内にありませんと出ました。
引き続き自分でも調べてみていますが、思い当たる改善案がありましたらご指導下さい。

Private Sub CommandButton1_Click()

Dim Yline As Long
Dim No As Long

No = Val(TextBox1.Text)

Yline = Worksheets("Sheet2").Range("B:B").Find(No, LookAt:=xlWhole).Row

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