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

Question

おせわになっております。

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
　　…
うまくいくようなのです。一度でもメソッドを抜けるとうまくいか
なくなるような感じです。

以上、わかりづらい説明で大変申し訳ありませんが、なにとぞお願い
致します。

pulsa · Accepted Answer

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を取ってきた時の動作を、別にする事ができます

fumufumu_2006 · Answer

質問と補足から、以下のような話でしょうか？
何かすると（たとえばシートの検索のコマンドボタンを押すと）、ユーザーフォームを表示する。
テキストボックスとコマンドボタンがある。
テキストを入力してコマンドボタンを押すと次々に検索する。

違ったら読み飛ばしてください。

ユーザーフォームのモジュール部に、確認用などで若干追加しましたが、補足で書いてあるプログラムをできるだけそのまま書いてみました。
ユーザーフォームにはテキストボックス(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

end-u · Answer

最初の質問文で
>仮に一列目で見つかったら、次は二列目以降から探そうとしています。
とあります。

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プロパティを使っても良いですし、
ダミーな非表示シートのセルにアドレスを書き込んだりしても良いかと思います。

pulsa · Answer

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なんかよりかなり高速化できますので、使いこなせれば強力な武器になります
がんばれ！

end-u · Answer

こんにちは。
それだけの情報では判断が難しいです。
最初の
>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

もし、こういう使い方なら、モジュールレベルの変数が初期化されるような処理がどこかにないですか？

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

pulsaです

この回答への補足

質問と補足から、以下のような話でしょうか？

この回答への補足

最初の質問文で

この回答への補足

Findは、Rangeのプロパティのひとつと考えると良いと思います

この回答への補足

こんにちは。

この回答への補足

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

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

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

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

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

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