EXCEL VBA 他のワークシートのデータを見るには？

質問

EXCEL VBAでsheet1のセルに何かデータが入力された時に、sheet2のどこかのセルに同じ文字列がないかを探したいと思います。

データの入力と同時に検索をかけるため、sheet1の部分にイベントプロシージャを定義し、
データが入力された時にそのルーチンの中からsheet2のデータを見に行きたいのですが、エラーが出てしまいます。
こういうことはできないのでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LineNo As Integer
Dim tmp_str As String
tmp_str = Target.Value
LineNo = Sheets("sheet2").Cells.Find(what:=tmp_str, lookat:=xlWhole).Row
MsgBox (LineNo)
End Sub

回答No.2

hana-hana3 · ベストアンサー

こんな感じでいかがですか？

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LineNo As Variant
Set LineNo = Sheets("sheet2").Cells.Find(what:=Target.Value, lookat:=xlWhole)
If Not LineNo Is Nothing Then MsgBox LineNo.Row
End Sub

お礼

ご回答ありがとうございます。
このやり方で行きます。

回答No.3

KenKen_SP

この辺は大丈夫ですか？

■ Excel VBA ヘルプ引用

　　引数 LookIn、LookAt、SearchOrder、および MatchByte の設定は、
　　このメソッドが使われるたびに保存されます。
　　次にこのメソッドを使うときに、これらの引数の指定を省略すると、
　　保存された設定が使われます。

つまり、Find メソッドを使う場合は上記のパラメータをちゃんと
指定しないと、前回の設定のまま検索が行われてしまう可能性があると
いうことです。

設定が違えば、「あるはずなのに？」ということもあり得ます。

または、データの末尾に不要なスペースなどがあって、一見同じデータ
にみえても、プログラム的に見れば別データとなっている事があります。

Private Sub Worksheet_Change(ByVal Target As Range)

　　Dim rFound As Range

　　' // 終了条件:: 値が変化したセルが複数の場合は中止
　　If Target.Count > 1 Then Exit Sub
　　' // 終了条件:: データ消去も中止
　　If Len(Target.Value) = 0 Then Exit Sub

　　' // 検索処理（Find メソッドはちゃんとパラメータ指定する）
　　Set rFound = Sheets("Sheet2").Cells _
　　　　　　　　 .Find(What:=Target.Text, _
　　　　　　　　　　　 LookAt:=xlWhole, _
　　　　　　　　　　　 LookIn:=xlValues, _
　　　　　　　　　　　 MatchByte:=False)
　　' // 結果出力
　　If rFound Is Nothing Then
　　　　' MsgBox "Not Found."
　　Else
　　　　MsgBox "RowNum :=" & CStr(rFound.Row)
　　　　Set rFound = Nothing
　　End If

End Sub

回答No.1

TAK_999

Findメソッドで対象文字（tmp_str）がSheet2の中で見つからないと
Nothingが返されます。Nothingに対してRowプロパティは使えませんか
らエラーになります。

あまりスマートな例ではありませんが、以下のコーディングではどうでしょう？
If ThisWorkbook.Sheets("Sheet2").Cells.Find(what:=tmp_str, lookat:=xlWhole) Is Nothing Then
MsgBox "見つかりません"
Else
LineNo = ThisWorkbook.Sheets("Sheet2").Cells.Find(what:=tmp_str, lookat:=xlWhole).Row
MsgBox (LineNo)
End If

お礼

ご回答ありがとうございます。
とりあえずsheet2に入力してあるデータだけを検索したので、
nothingになるはずはないのですが…。