FIND関数について教えてください

質問

EXCEL VBAを使って、検索ツールを作成中です。

１，sheet1のセルA1に入力されたものをsheet2,3の特定の列から検索して、結果のすべてをsheet1 A2以下に表示する。
2,１の検索結果（A2以下）をそれぞれsheet2,3から更に検索する。
　※sheet2,3のA列からsheet1A1を検索し、同じ行のC,D列のデータをsheet1A2以下に持ってくる
　※A2以下の検索結果は複数。sheet2,3のA列からsheet1A2以下を検索し、C列から横に更なる検索結果があればそれを表示して行きたい。

まず書いたのは下記のようなもの
Sub 検索()
Dim FoundCell As Range, FirstCell As Range
Set FoundCell = Cells("A:A") .Find(What:=sh1.range("A1").value)
If FoundCell Is Nothing Then
MsgBox "見つかりません"
Exit Sub
Else
Set FirstCell = FoundCell
FoundCell.Resize(1, 2).Copy Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Do
Set FoundCell = Cells.FindNext(FoundCell)
If FoundCell.Address = FirstCell.Address Then
Exit Do
Else
FoundCell.Resize(1, 2).Copy Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End If
Loop
End Sub

検索1（）として
If FoundCell Is Nothing Then
MsgBox "見つかりません"
Exit Sub
を、
If FoundCell Is Nothing Then
検索2
と表記を変えて実行したのですが、見つからなかった場合、「見つかりません」のメッセージと共に「実行エラー5」で「 Set FoundCell = Cells.FindNext(FoundCell)」が示されます。

また、A2以下という曖昧な検索セルを指定する方法が分かりません。
　set str=sheet1.Cells(i,1).value
というようなこともしてみたのですが、エラーになってしまいました。

なにかアイディアを教えてください。

回答No.1

dreamscats0204 · ベストアンサー

通りすがりのVBAビギナーです。

初めにお断りしておきますが、質問の内容と違った答えを書いていたらすみません。

Sub 検索()
＿＿Dim FoundCell As Range, FirstCell As Range
＿＿''どのシートからどうやって実行するのかわからなかったので、Sheet1を
＿＿''Withステートメントでまとめてみました（Sheet1という名前の他のシートがあれば
＿＿''Worksheets("Sheet1")に変更して下さい
＿＿With Sheets("Sheet1")
＿＿＿＿''Sheet1のA1が空欄だったらここで終了します
＿＿＿＿If .Range("A1").Value = "" Then
＿＿＿＿＿＿MsgBox "検索条件が設定されていません", vbExclamation
＿＿＿＿＿＿Exit Sub
＿＿＿＿End If
＿＿＿＿''=====================ここから検索開始(Sheet2)=========================
＿＿＿＿''ここで間違っていたポイントは↓の範囲指定がCells("A:A")となっていた所です
＿＿＿＿''セル範囲の指定はRangeオブジェクトで行います
＿＿＿＿Set FoundCell = Sheets("Sheet2").Range("A:A").Find _
＿＿＿＿＿＿＿＿＿＿＿＿(What:=.Range("A1").Value)
＿＿＿＿If Not FoundCell Is Nothing Then
＿＿＿＿＿＿Set FirstCell = FoundCell
＿＿＿＿＿＿''Cellsプロパティで最終行を取得する所ですが、Rows.Countも
＿＿＿＿＿＿''シートオブジェクトを指定します
＿＿＿＿＿＿FoundCell.Resize(1, 2).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
＿＿＿＿＿＿Do
＿＿＿＿＿＿＿＿Set FoundCell = Cells.FindNext(FoundCell)
＿＿＿＿＿＿＿＿If FoundCell.Address = FirstCell.Address Then
＿＿＿＿＿＿＿＿＿＿Exit Do
＿＿＿＿＿＿＿＿Else
＿＿＿＿＿＿＿＿＿＿FoundCell.Resize(1, 2).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
＿＿＿＿＿＿＿＿End If
＿＿＿＿＿＿Loop
＿＿＿＿End If
＿＿＿＿''======================ここから検索開始(Sheet3)=========================
＿＿＿＿''こちらは見つからなかったらメッセージを出して処理を抜けます
＿＿＿＿Set FoundCell = Sheets("Sheet3").Range("A:A").Find _
＿＿＿＿＿＿＿＿＿＿＿＿(What:=.Range("A1").Value)
＿＿＿＿If Not FoundCell Is Nothing Then
＿＿＿＿＿＿Set FirstCell = FoundCell
＿＿＿＿＿＿FoundCell.Resize(1, 2).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
＿＿＿＿＿＿Do
＿＿＿＿＿＿＿＿Set FoundCell = Cells.FindNext(FoundCell)
＿＿＿＿＿＿＿＿If FoundCell.Address = FirstCell.Address Then
＿＿＿＿＿＿＿＿＿＿Exit Do
＿＿＿＿＿＿＿＿Else
＿＿＿＿＿＿＿＿＿＿FoundCell.Resize(1, 2).Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
＿＿＿＿＿＿＿＿End If
＿＿＿＿＿＿Loop
＿＿＿＿Else
＿＿＿＿＿＿MsgBox "見つかりませんでした", vbInformation
＿＿＿＿＿＿Exit Sub
＿＿＿＿End If
＿＿End With
End Sub

解らない所があったり動かなかったりしたら、再度質問お願いします。
※半角スペースが消えて見づらいので、半角スペース2個をアンダーバーに置き換えてます

補足

ありがとうございます。
後で試してみます。
できれば、検索結果を更に検索する、Cells(i,1).valueを検索するようなコードを教えていただけるとありがたいです。

回答No.2

dreamscats0204

Cells(i,1).Value(例えばですが)で検索条件を行方向で複数指定したい場合は、

Sub 検索()
＿＿Dim i As Long
＿＿For i = （初めの行番号） To （終りの行番号）　''使う数値に置き換えてください
＿＿＿＿''今回の処理（要修正）　※.Range("A1").Value ⇒ .Cells(i,1).Value
＿＿Next i
End Sub

みたいな感じでいいんでしょうか？

ただし、そうなるとA列に全ての結果がコピーされるので見辛いと思います。

A列に検索条件を列挙して、対応した行のB列以降にその行の結果を出力していった方が分かりやすいかもしれません。

内容を正確に把握できていないので、この程度しか回答できずすみません。

追記：前回の投稿のコードですが、Find関数は引数が必要な場合がありますので、その場合適宜追加してお使いください。

補足

やはりそれでいんですよね。元のコードが間違っていたので、エラーになったようです。

前回の回答ですが、sheet2にあってもsheet3にないと「見つかりません」となるようです。
説明が悪くて申し訳ありません。sheet2,3それぞれを検索するのですが、sheet2になかったらsheet3を探すという風にしたかったのです。

頂いたコードを少し修正したらできましたので、前回の回答をベストアンサーとさせていただきます。