VBA Findの使い方

質問

以下のコードは、最初の見つかったXYZに対しては、正しく処理されますが、
その後はうまくいきません。

なぜ、なのでしょうか？
また、どうすれば見つかったすべてのXYZに対して処理ができるのでしょうか？
（B列からxyzを検索し、その隣のセルに99を入力する）

Sheets("Sheet1").Select
Do While (True)
Columns("B:B").Select
Set mySelect = Selection.Find(What:="XYZ")
If mySelect Is Nothing Then Exit Do

mySelect.Offset(, 1).Value = 99
Loop
End Sub

回答No.6

real beatin（@realbeatin）

No.2 です。説明文について【訂正】です。
｜
｜　ご提示のコードでは、
｜　> Columns("B:B").Select
｜　繰り返し、B列を選択し直している　→　常に【先頭】セルはB1になる。
｜　> Set mySelect = Selection.Find(What:="XYZ")
｜　引数Afterが省略されている為、
｜　【先頭】セルを基準に[次を検索]する。
｜　つまり、B1を基準に次に見つかるセルを検索することを
｜　繰り返していることになり、その次、へ進むことが出来ていません。

誤）　アクティブセル
　↓
正）　先頭セル

失礼しました。

手作業で[次を検索]する場合の説明として以前書いたものを
そのまま転用してしまって間違いに気付きませんでした。
ＶＢＡでは、
　Selection.Find
引数Afterを省略した場合の基準セルは、
　Selection(1)
に位置する【先頭】セルです。

以上、訂正コメントでした。

回答No.5

kagakusuki

回答No.4です。
　失礼致しました。先程の回答のVBAマクロには一部バグがありましたので訂正致します。

Sub QNo9126961_VBA_Findの使い方()

Const SearchValue = "XYZ"
Const SearchRangeAddress = "B:B"
Dim mySelect As Range, SearchRange As Range, FirstAddress As String

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

With Sheets("Sheet1")
Set SearchRange = .Range(SearchRangeAddress)
Set mySelect = SearchRange.Find( _
What:=SearchValue, After:=SearchRange.Resize(1, 1) _
, LookIn:=xlValues, LookAt:=xlWhole _
, SearchOrder:=xlByColumns)
If mySelect Is Nothing Then
MsgBox SearchRangeAddress _
& "の範囲内には、検索値として設定されている" _
& vbCrLf & vbCrLf & SearchValue & vbCrLf & vbCrLf _
& "と同じ値を持つセルは見つかりませんでした。" _
& vbCrLf & vbCrLf & "マクロを終了致します。" _
, vbInformation, "該当セル無し"
Else
FirstAddress = mySelect.Address
Do
mySelect.Offset(, 1).Value = 99
Set mySelect = SearchRange.FindNext(mySelect)
If mySelect.Address = FirstAddress Then Exit Do
Loop
End If
End With

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With

End Sub

回答No.4

kagakusuki

複数のセルを検索する際にFindを使って行う場合には、最初の検索のみFindで行い、Do～Loopの中ではFindNextを使う様にします。

Sub QNo9126961_VBA_Findの使い方()

Const SearchValue = "XYZ"
Const SearchRangeAddress = "B:B"
Dim mySelect As Range, SearchRange As Range, FirstAddress As String

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

With Sheets("Sheet1")
Set SearchRange = .Range(SearchRangeAddress)
Set mySelect = SearchRange.Find( _
What:=SearchValue, After:=ActiveCell _
, LookIn:=xlValues, LookAt:=xlWhole _
, SearchOrder:=xlByColumns)
If mySelect Is Nothing Then
MsgBox SearchRangeAddress _
& "の範囲内には、検索値として設定されている" _
& vbCrLf & vbCrLf & SearchValue & vbCrLf & vbCrLf _
& "と同じ値を持つセルは見つかりませんでした。" _
& vbCrLf & vbCrLf & "マクロを終了致します。" _
, vbInformation, "該当セル無し"
Else
FirstAddress = mySelect.Address
Do
mySelect.Offset(, 1).Value = 99
Set mySelect = SearchRange.FindNext(mySelect)
If mySelect.Address = FirstAddress Then Exit Do
Loop
End If
End With

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With

End Sub

回答No.3

f272

すでに質問にたいする回答は出ているので，別のアドバイス。
Findなんて遅いから使うのはやめたほうがいいよ。
Sub FindXYZ2()
Sheets("Sheet1").Select
Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
ary = rng
For ix = LBound(ary) To UBound(ary)
If ary(ix, 1) Like "*XYZ*" Then
rng.Cells(ix, 1).Offset(, 1) = 99
End If
Next
End Sub

回答No.2

real beatin（@realbeatin）

こんにちは。

とりあえず、こんな感じでしょうか？

' ' ///

Sub ReW9126961()
Dim c As Range
Dim n1stRow As Long
　　Worksheets("Sheet1").Select
　　With Range("b:b")
　　　　Set c = .Find( _
　　　　　　What:="XYZ", After:=.Cells(.Cells.Count), _
　　　　　　LookIn:=xlValues, LookAt:=xlWhole, _
　　　　　　SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
　　　　　　MatchCase:=True, SearchFormat:=False)
　　　　If Not c Is Nothing Then
　　　　　　n1stRow = c.Row
　　　　　　Do
　　　　　　　　c.Offset(, 1).Value = 99
　　　　　　　　Set c = .FindNext(c)
　　　　　　Loop Until c.Row = n1stRow
　　　　End If
　　End With
End Sub

' ' ///

range.FindNext メソッドが、
前回の検索結果を基準にして[次を検索]する方法です。
[次を検索]では、同じセルを何度でも通りますから、
放っておくと無限ループになるので対策が必要です。
今回の設問では単列範囲なので、
.Row プロパティで行位置について、
最初に見つかったセル、と、今見つかったセル、とで
比較して同じ位置に至ったらループを抜けます。
因みに、range.Find メソッドの各種引数についてですが、
直前に実行した際のオプション設定（引数指定）
を基本的に踏襲する仕様ですので、
ＶＢＡで書く時は、一連の処理の中で最初に
range.Find メソッドを実行する記述では、
引数を省略せずに書かないと、痛い目に合うことが多々あります。
一連の処理の中で２回め以降は、最初に命令したオプション設定が
踏襲されるので省略可能になります。
また、
今回の設問では隣のセルを書き換える処理だけなので構いませんが、
見つかったセルそのものを書き換える処理をする時などは、
次に見つかる筈のセルが無い場合もありますので、
そういう場合は、
　　　　　　Do
　　　　　　　　c.Value = Rnd()
　　　　　　　　Set c = .FindNext(c)
　　　　　　　　If c Is Nothing Then Exit Do
　　　　　　Loop Until c.Row = n1stRow
のように書くことになります。

> 以下のコードは、最初の見つかったXYZに対しては、正しく処理されますが、
> その後はうまくいきません。
>
> なぜ、なのでしょうか？

ご提示のコードでは、
> Columns("B:B").Select
繰り返し、B列を選択し直している　→　常にアクティブセルはB1になる。
> Set mySelect = Selection.Find(What:="XYZ")
引数Afterが省略されている為、
アクティブセルを基準に[次を検索]する。
つまり、B1を基準に次に見つかるセルを検索することを
繰り返していることになり、その次、へ進むことが出来ていません。

以上です。

回答No.1

m_and_dmp

Find 関数は一つ見つかれば次を見つけようとしないのでは？
それに、Loopで戻ったとき、また同じ場所のxyz を見つけるのでいつになってもNothing にならないと思います。試してみましたが、マクロが何時になっても終了しませんでした。（ESCで中断させました。）
つぎのようにすると、期待した動きをすると思います。
Sub FindXYZ()
Sheets("Sheet1").Select
I = 1
Do While (True)
Range(Cells(I, 3), Cells(1000, 3)).Select
Set mySelect = Selection.Find(What:="XYZ")
If mySelect Is Nothing Then Exit Do
mySelect.Offset(, 1).Value = 99
myRow = mySelect.Row
I = myRow + 1
Loop
End Sub

お礼

回答ありがとうございます。
Findは、常に最初から検索するようですね