ExcelVBA Findメソッドで検索のスタート位置について

質問

おせわになっております。

Findメソッドを用いて、先頭から順に値を検索しようとしています。

Set rngSearch = rngList.Find("ゴルフ", rngSearch, , xlPart)
↑
このような方法で、該当するものを一つ一つ探そうとしています。
つまり、仮に一列目で見つかったら、次は二列目以降から探そうと
しています。

FindNextなどを使用しないのは、連続で求めるためではなく、
ボタンを押したときに一つずつ検索するからです。

しかし、この書式ではスタート地点にした、次のセルから検索する
はずが、スタート地点に指定したセルから検索してしまい、
何度行っても同じセルばかりを検索して返してしまいます。
Offsetなどで一行ずつずらしても、同じ場所からしか検索が
始まりません。

これはなぜなのでしょうか？

念のため、他のメソッドなどでは決してrngSearch は代入等の
操作はしておりません。

ちなみに、同じメソッドの中で同じ書式を繰り返すと
　　Set rngSearch = rngList.Find("ゴルフ", rngSearch, , xlPart)
MsgBox rngSearch.Value
　　Set rngSearch = rngList.Find("ゴルフ", rngSearch, , xlPart)
MsgBox rngSearch.Value
　　Set rngSearch = rngList.Find("ゴルフ", rngSearch, , xlPart)
MsgBox rngSearch.Value
　　Set rngSearch = rngList.Find("ゴルフ", rngSearch, , xlPart)
MsgBox rngSearch.Value
　　…
うまくいくようなのです。一度でもメソッドを抜けるとうまくいか
なくなるような感じです。

以上、わかりづらい説明で大変申し訳ありませんが、なにとぞお願い
致します。

回答No.5

pulsa · ベストアンサー

pulsaです

No.3の方のおっしゃるとおり、No.1の方への返答で示されたコード
>Set rngList = .UsedRange.Columns(7)
では、7列目しか検索対象になっていません
これが、列が移動しない原因でしょう
簡単に言うと
Columns(7).Select
Set rngSearch = Selection.Find(stKeyword, rngSearch, , xlPart, xlByRows, xlNext)
と変わりません
Set rngList = .UsedRange
で良いと思います（.UsedRangeが実際はなんなのか、は新たな謎ですが＾＾；）

他の部分はきちんとできているようなので、これだけで目的の動作が可能なはずです
あとは、エラーをどうするかです
今回の場合、同じRangeが検索結果=他に見つかっていない　はエラーです
つまりNothing以外に、同じRangeが検索結果もエラーとして拾う必要があります
対応は簡単でしょう
検索結果をいきなりrngSearchにセットするのではなく、別な変数に入れておいて、『Is』でrngSearchと比較し、結果がFalseであれば、その時点でrngSearchにセットすればいい事です

コードを示します

Dim FindRange As Range
Set FindRange = rngList.Find(stKeyword, rngSearch, , xlPart, xlByRows, xlNext)

If Not (FindRange Is Nothing) And Not (FindRange Is rngSearch) Then
　　Set rngSearch = FindRange
　　Call review(rngSearch.Offset(0, -4))
End If

返答されたコードを変形しています
Exit Subをそこかしこで使うのが好きでないので、Notで反転しています
And　を分解すれば、見つからない時と同じRangeを取ってきた時の動作を、別にする事ができます

お礼

皆様、色々ありがとうございました。

本来は仕様とは違うのですが、Columns(7)を消したところ、
とりあえずスタートポイントの移動は確認できました。

一行単位での動作はいまだにおかしいままですが、とりあえず
目的は達成できたので、この質問は終了させていただきます。

本当にありがとうございました。

補足

回答していただいた皆様方、何度もありがとうございます。

私の説明の悪さで誤解をさせてしまい、本当に申し訳ありません。

まず、私の表現で勘違いしていまして、列と行を間違えていました。
要は、縦一行の中で検索したいため、特定の七列目のみで検索を
かけています。その為、余計なColumnに検索が移らぬよう、Columns(7)
で絞り込みをしています。

その処理の中で、いちばん上のセルから検索し、仮に二番目に
見つかったら、次は三番目のセルから再び検索…ということを
行っているつもりです。

ためしに、検索をする先頭セルを、下に一つずらして処理させて
みると、一回目はうまくいって次のセルに移るのですが、二回目以降は
やはり動かずに同じセルばかり拾ってきてしまいます。

…これは何となくですが、なんか固定で『2』行目から検索を
始めているような感じに思えます…が、それはただの
思いすごしでしょうか^^;

Findメソッドの説明には、二番目の引数、Afterには、そこで指定した
『次のセル』から検索とありますので、その通りで行くと、コードとしては間違っていないとは思ったのですが…、うまくいきませんでした。
一応Offsetして確かめてみたコードを再度掲載します。

たびたびすみませんが、再びお力添えを何卒お願いいたします。

'フォーム初期化時
With ThisWorkbook.Worksheets("Data")
Set rngList = .UsedRange.Columns(7)
Set rngSearch = rngList.Cells(1, 1)
End With

Private Sub searchKeyword()

If (Me.fldSummary.Text = "") Then
Exit Sub
End If

Dim stKeyword As String

stKeyword = Me.fldSummary.Text

With ThisWorkbook.Worksheets("Data")

' Set rngSearch = rngList.Find(stKeyword, rngSearch, , xlPart, xlByRows, xlNext)
Dim rngBuff As Range

Set rngBuff = rngList.Find(stKeyword, rngSearch.Offset(1, 0), , xlPart, xlByRows, xlNext)

If (rngBuff Is Nothing) Then
MsgBox "見つからないよ"
Exit Sub
End If

Set rngSearch = rngBuff

'Call review(rngSearch.Offset(0, -4))

End With
End Sub

回答No.4

fumufumu_2006

質問と補足から、以下のような話でしょうか？
何かすると（たとえばシートの検索のコマンドボタンを押すと）、ユーザーフォームを表示する。
テキストボックスとコマンドボタンがある。
テキストを入力してコマンドボタンを押すと次々に検索する。

違ったら読み飛ばしてください。

ユーザーフォームのモジュール部に、確認用などで若干追加しましたが、補足で書いてあるプログラムをできるだけそのまま書いてみました。
ユーザーフォームにはテキストボックス(fldSummary)とコマンドボタン(CommandButton1)があるとします。
で、動いてるみたいなんですが・・・
どこか間違ってますか？

Option Explicit
Dim rngSearch As Range
Dim rngList As Range

Private Sub UserForm_Activate()
With ThisWorkbook.Worksheets("Data")
.Activate '後で確認のためselectするので一応Activateにしておきます
Set rngSearch = .UsedRange.Columns(7).Cells(1, 1)
Set rngList = ThisWorkbook.Worksheets("Data").UsedRange.Columns(7)
End With
End Sub

Private Sub CommandButton1_Click()
searchKeyword
End Sub

Private Sub searchKeyword()
If (Me.fldSummary.Text = "") Then
Exit Sub
End If
Dim stKeyword As String
stKeyword = Me.fldSummary.Text
With ThisWorkbook.Worksheets("Data")
Set rngSearch = rngList.Find(stKeyword, rngSearch, , xlPart, xlByRows, xlNext)
If (rngSearch Is Nothing) Then
MsgBox "ありません" '無かった場合、一応黙っているのは何なので
Set rngSearch = ThisWorkbook.Worksheets("Data").UsedRange.Columns(7).Cells(1, 1) 'Nothingのままだと次の検索でエラーになるので
Exit Sub
End If
'Call review(rngSearch.Offset(0, -4))
rngSearch.Select 'とりあえず選択部を表示
End With
End Sub

補足

fumufumu_2006さん、ありがとうございます。

ほぼ、記述してある通りです。

この通りでうまくいくはずなのですが…。

うまくいきません。

何かお心当たりがありましたら、ぜひお力添えをお願いいたします。

回答No.3

end-u

最初の質問文で
>仮に一列目で見つかったら、次は二列目以降から探そうとしています。
とあります。

ANo.1の補足に
>それ以外の場所では、前回記述したメソッド以外では、
>rngList、及びrngSearchともに使用しておりません。
とあります。
rngListが固定なら、常に 7 列目しか検索してくれません。
7列目に検索値が１つしかないという事は考えられないのですか？
>ちなみに、同じメソッドの中で同じ書式を繰り返すとうまくいくようなのです。
と書いてありますので、そのケースはないのかもしれませんが念の為。

また、検証するにはごくシンプルなコードでテストしてみる事が必要でしょう。
例えば、メソッドを抜けても次検索できる例をANo.1で提示しました。
新規Bookに適当なデータを入れて、標準モジュールに置いて試してみると良いと思います。
うまく動くようなら、問題のコードでは変数の初期化が関係している可能性が大きいと思うのですが。
（変数の初期化は明示的に = Nothing とするだけでなく、何らかのタイミングで破棄される事も含みます）

[VBA] Public 宣言された変数の有効期間
http://support.microsoft.com/kb/408871/ja

ただ、rngSearch が初期化されてNothingであれば検索時にエラーがかかるはずなのです。
　　With ThisWorkbook.Worksheets("Data")
　　　　MsgBox rngSearch.Address
　　　　Set rngSearch = rngList.Find(stKeyword, rngSearch, , xlPart, xlByRows, xlNext)
（エラー制御されていなければ。）

もし、変数初期化が関係しているなら、検索結果アドレスをどこかに記録しておくようにすれば良いです。
それは
Private Sub UserForm_Initialize()
　　With ThisWorkbook.Worksheets("Data")
　　　　Set rngSearch = .UsedRange.Columns(7).Cells(1, 1)
　　　　Me.Tag = rngSearch.Address
：

Private Sub searchKeyword()
　　If (Me.fldSummary.Text = "") Then
　　　　Exit Sub
　　End If
　　Dim stKeyword As String
　　stKeyword = Me.fldSummary.Text
　　With ThisWorkbook.Worksheets("Data")
　　　　Set rngSearch = rngList.Find(stKeyword, .Range(Me.Tag), , xlPart, xlByRows, xlNext)
　　　　If (rngSearch Is Nothing) Then
　　　　　　Exit Sub
　　　　End If
　　　　Me.Tag = rngSearch.Address
：
こんな感じで、UserFormのTagプロパティを使っても良いですし、
ダミーな非表示シートのセルにアドレスを書き込んだりしても良いかと思います。

補足

end-uさん、たびたびありがとうございます。

上記の方法でも試してみましたが、やはりうまくいきません。

どうしても二行目で止まってしまいます（二行目に該当データがあると
ずっと二行目ばかり返す)。

ちなみに、行と列の言葉を組みちがえていました。
大変申し訳ありません。

引き続き、お力添えをいただければ幸いです。

回答No.2

pulsa

Findは、Rangeのプロパティのひとつと考えると良いと思います
つまり、探すんではなく、有るところを教えてくれます
これが、戻り値がRange『Object』で帰る理由です（無いときはNothing）

ご質問の場合、No.1の方がおっしゃるとおり
これだけの情報では判断が難しいです
上に書いたとおり検索範囲のRangeの内、最初に該当するRangeを返すので、ご質問の内容は最初に見つけた"ゴルフ"のRangeを返します
つまりセルを教えてくれるだけ、です

ですので、実行中のメソッドで複数呼ぶ（この場合、rngSearchが生きてる間と解釈してもOK）と成功するのに、メソッドを抜けると上手く行かないとなります
理由は　rngSearch　がクリアされる為で、Findの引数でAfterが省略されているときは、指定範囲の左上から検索するとなっています。（因みにNullの場合も左上が検索開始位置になる）

回避方法はいくつかありますが、まず
rngSearch に値が入った直後に、Actveにする方法があります

ただ、この方法は当然、該当セルが存在しない場合、エラーです

ですので、次のようにします

おそらく現状　rngList　には　Cells　か、ざっくりしたRange（行列複数）が指定してあるように思いますが、質問からすると列ごとに検索していくようですので、それであれば　rngList　を検索が成功するたびに、1列ずつずれるように指定するほうがいいでしょう

コードを示します
　　Dim rngSearch
　　Set rngSearch = Columns(1).Find("ゴルフ", , , xlPart) '一回目
　　If Not rngSearch Is Nothing Then
　　　　Set rngSearch = Columns(2).Find("ゴルフ", rngSearch, , xlPart) '二回目
　　　　If Not rngSearch Is Nothing Then
　　　　　　Set rngSearch = Columns(3).Find("ゴルフ", rngSearch, , xlPart) '三回目
　　　　　　・
　　　　　　・
　　　　　　・
　　　　End If
　　End If

ただ、これでは面白くありませんので
　　Dim rngSearch
　　Dim iCnt As Integer　　
　　Set rngSearch = Columns(1).Find("ゴルフ", , , xlPart) '一回目
　　If Not rngSearch Is Nothing Then
　　　　MsgBox rngSearch.Value
　　　　For iCnt = 2 To Range("A1").CurrentRegion.Columns.Count
　　　　　　Set rngSearch = Columns(iCnt).Find("ゴルフ", , , xlPart) '二回目以降
　　　　　　If Not rngSearch Is Nothing Then
　　　　　　　　Exit For
　　　　　　Else
　　　　　　　　MsgBox rngSearch.Value
　　　　　　End If
　　　　Next iCnt
　　End If

Findは意外と難しく、ハマるんですが、Forなんかよりかなり高速化できますので、使いこなせれば強力な武器になります
がんばれ！

補足

pulsaさん、ありがとうございます。
1の方にもお返事をしたのですが、rngList,rngSearchは外部で
宣言し、Form_Loadでその値を設定した後、他では決して
操作していません。
フォームを閉じたりしない限りは、外部変数は初期化されないと
思うのですが…。
いただきましたサンプルを元に、いろいろと調べてみますが、
もう少しお力添えいただければ幸いです。

念のため、メソッドを書き込みます。

Private Sub searchKeyword()

If (Me.fldSummary.Text = "") Then
Exit Sub
End If

Dim stKeyword As String

stKeyword = Me.fldSummary.Text

With ThisWorkbook.Worksheets("Data")

Set rngSearch = rngList.Find(stKeyword, rngSearch, , xlPart, xlByRows, xlNext)

If (rngSearch Is Nothing) Then
Exit Sub
End If

Call review(rngSearch.Offset(0, -4))

End With
End Sub

以上、よろしくお願い申し上げます。

回答No.1

end-u

こんにちは。
それだけの情報では判断が難しいです。
最初の
>Set rngSearch = rngList.Find("ゴルフ", rngSearch, , xlPart)
引数Afterの rngSearch はどのようにSetされているのですか？

Option Explicit
Dim rngSearch As Range
Dim rngList　 As Range

Sub try()
　　If rngList Is Nothing Then
　　　　Set rngList = ActiveSheet.UsedRange
　　End If
　　If rngSearch Is Nothing Then
　　　　Set rngSearch = rngList.Cells(1)
　　End If
　　Set rngSearch = rngList.Find("*", rngSearch, , xlPart)
　　MsgBox rngSearch.Value
End Sub

もし、こういう使い方なら、モジュールレベルの変数が初期化されるような処理がどこかにないですか？

補足

end-uxyさん、ありがとうございます。
説明が不足しており、大変申し訳ありません。
rngList及び、rngSearchは、Form_Loadの時点で
外部変数としてセットしています。
With ThisWorkbook.Worksheets("Data")
Set rngSearch = .UsedRange.Columns(7).Cells(1, 1)
Set rngList = .UsedRange.Columns(7)
End With
上記の記述で、Columns一行分を範囲として取得し、その先頭を
rngSearchの起点としてセットしています。

それ以外の場所では、前回記述したメソッド以外では、
rngList、及びrngSearchともに使用しておりません。

以上、なにとぞお願い申し上げます。