ExcelVBA(マクロ)でセルを検索・置換する方法/Find、Replaceメソッドの使い方

little-boy IT

マクロを使っていくと、特定の値を検索してそれに干渉したり、条件に該当するセルだけ置換したりといった操作が要求される場面が出てきます。そうした場合は、FindメソッドやReplaceメソッドを使うことで対応できるかもしれません。

スポンサーリンク

Findメソッドの使い方

条件に合うセルを検索し、その位置情報を取得
検索範囲.Find(検索内容)
find1
A列の数字のいずれかを選択した状態でマクロを実行すると、C列の同じ数字を検索し、色をつけるというマクロを組んでみたいと思います。
find2
Findメソッドを使う場合、①まず変数を作っておきます。As Rangeを付けるほうが正式ですが、なくても動きます。
find3
②その変数にSetを使って何か代入することを示した上で、
find4
③検索範囲を入力します。もし別のシートなら、Sheets(“シート名”).Range(範囲)のようにします。
find5
④最後に何を検索するかを指定します。
しかる後変数に対して何か処理を行います。
find6
今作ったSet FoundCell = Range(“C1:C14”).Find(ActiveCell.Value)(C1~C14検索し、アクティブセルの値値が一致するセルを見付けて変数FoundCellに代入というコードを実行すると、たとえばA列で「5」を選んでいれば、
find7
ActiveCellの値である「5」と同じものがC列で検索され、最初に一致するC5セルが変数FoundCellに代入されます。
find8
で、そのFoundCellに対して色を変えるマクロを実行すると、
find9
FoundCell=Range(“C5”)なので、C5セルの色が変わります。
このようにfindメソッドを使うと、「指定した値のあるセルを見付けて」「そのセルやその周囲に処理を行う」ことができます。

値が見つからない場合の処理

find10
上記のようなコードでもし値が見つからない場合、
find11
「実行時エラー91 オブジェクト変数または With ブロック変数が設定されていません。」と出ます。
「指定した値がない」というエラーです。
find12
Findメソッドで検索値が見付けられない場合、「Nothing」という値が返されます。
なのでエラーを出さないようにするために、発見できない場合の処理としてIf FoundCell Is Nothing Thenを記述します。
検索値がNothingかどうかを判定する時は、「=」ではなく「Is」を使います。
find13
今回は見つからない場合何もしないことにして、そのままElseを置き、その下に色を変えるコードを置いて、End Ifで閉じます。
find14
これで検索値が見つからない場合何もせず、エラーも出ないマクロとなります。
' vba
Dim FoundCell
' ActiveCellの値をC1~14で検索
Set FoundCell = Range("C1:C14").Find(ActiveCell.Value)
If FoundCell Is Nothing Then' 一致しない場合
Else
' 検索して見付けたセルにアクション
FoundCell.Interior.ColorIndex = 20
End If

Findメソッドの引数一覧

ここからはより詳しくFindメソッドの機能を見ていきます。上で使っているのはFindメソッドの引数「What」のみで、その他にも引数を色々と指定できますが、What以外はすべて省略可能です。

条件に合うセルを検索し、その位置情報を取得
検索範囲.Find(What,After,LookIn,LookAt,SearchOrder,
SearchDirection,MatchCase,MatchByte,SearchFormat)

※What以外は省略可
※LookIn、LookAt、SearchOrder、MatchCase、MatchByteは
省略した場合、前回の設定を引き継ぐ
引数名 意味 値の保存
What 検索する文字列 文字列
After 開始位置 位置(Rangeなどで)
LookIn 検索対象 XlFormula(数式)、XlValue(値)、XlComments(コメント)
LookAt セル内容が完全に同一か XlPart(部分一致検索)、XlWhole(完全一致検索)
SearchOrder 検索方向 xlByRows(行方向検索)、xlByColumns(列方向検索)
SearchDirection 検索順序 xlNext(通常の順番)、XlPrevious(逆順)
MatchCase 大文字と小文字 True(区別する)、False(区別しない)
MatchByte 全角と半角 True(区別する)、False(区別しない)
SearchFormat 書式検索 True(する)、False(しない)

What(検索する文字列)

findメソッド vba excel1
引数Whatについては既に説明した通り。せっかくなので別の例として、VLookUp的なFindメソッドを組んでみます。
' vba

Dim foundCell
Set foundCell = Range("A1:A10").Find("3").Next
msgbox foundCell
FindメソッドにNextプロパティをくっつければ、
findメソッド vba excel2
A1~A10の中で「3」のあるセルの右のセルの値を表示することができます。
' vba

Dim foundCell
Set foundCell = Range("A1:A10").Find(What:="3").Next
msgbox foundCell
この時、Findメソッドの()内に置いた「何を検索するか」、この場合3が引数Whatに指定した値となります。
引数名を指定し、Range(“A1:A10”).Find(What:=”3″).Nextと書いても同じ意味になります。もし他の引数も使用するのであれば、このように書いたほうがわかりやすいかもしれません。

After(開始位置)

find after vba excel1
Afterを使うと、どこを起点に検索を開始するかが決められます。
たとえば図のように検索条件に合致するセルがふたつある場合、通常セルの頭に近いほうだけがヒットしますが、
find after vba excel2
コードをSet foundCell = Range(“A1:A10″).Find(What:=”3”, After:=Range(“A3”)).Nextと修正した場合、
find after vba excel3
A3の次のセルが検索の起点となり、そこから下に向かって検索が進んでいきます。
find after vba excel4
結果として2番目に該当する検索結果がヒットする、というわけです。
find after vba excel5
もし一番下まで行っても何も見つからない場合、また頭に戻って起点に向かって検索が進みます。

LookIn(検索対象)

lookin find vba1
LookInを指定すると、検索画面でいう検索対象の設定を行えます。
' vba

'XlFormula = 数式で検索
Set foundCell = Range("A1:A10"). _
Find(What:="3", LookIn:=XlFormula)
MsgBox foundCell
LookInはXlFindLookIn列挙型定数で指定し、それぞれ数式XlFormulaXlValueコメントXlCommentsとなります。
lookin find vba3
数式(XlFormula)の場合、セルに直接「3」と入力されていれば普通にヒットするんですが、
lookin find vba4
参照式や関数の結果が「3」のセルはヒットしません。=1+2の結果3になっているような場合は無視されます。
lookin find vba5
値(XlValue)であればそういうのもヒットする、といった区分けになります。
lookin find vba6
コメント(XlComments)はコメントだけを検索します。
lookin find vba7
※LookInは一度設定すると、変更しない限り次のFindでも同じ設定で実行されます。コメントで検索した後、LookInを省略してFindメソッドを実行すると設定が初期化されず、再度コメントが検索されるという意味です。
確実を期すなら、毎回LookInは指定する必要があります。

LookAt(完全/部分一致)

vba find lookat1
引数LookAtは、検索画面でいうところの「セル内容が完全に同一であるものを検索する」にあたります。
' vba

' XlPart = 部分検索
Set foundCell = Range("A1:A10"). _
Find(What:="10", Lookat:=xlPart).Next
MsgBox foundCell
値はXlLookAt列挙型定数で指定します。
部分一致検索の場合はXlPartとなり、
vba find lookat2
「0」で検索しても、「10」をヒットすることが可能です。
vba find lookat3
完全一致検索の場合はXlWholeとなり、この場合「0」を含むだけではヒットしません。
vba find lookat4
値が完全に一致するセルがあって、はじめてヒットします。

※LookAtは一度設定すると、変更しない限り次のFindでも同じ設定で実行されます。

SearchOrder(検索方向)

vba find searchorder1
引数SearchOrderは、検索画面でいうところの「検索方向」にあたります。
' vba
'行方向
Set foundCell = Range("A1:A10"). _
Find(What:="3", SearchOrder:=xlByRows)
'列方向
Set foundCell = Range("A1:A10"). _
Find(What:="3", SearchOrder:=xlByColumns)
値はXlSearchOrder列挙型定数で指定し、xlByRowsなら行方向(通常左から右)、xlByColumnsなら列方向(通常上から下)に検索が進みます。
vba find searchorder3
範囲内でヒット対象が複数あった場合、xlByRowsなら通常こっちのセルが先に、
vba find searchorder2
xlByColumnsなら通常こっちのセルが先にヒットします。

※SearchOrderも一度設定すると、変更しない限り次のFindで同じ設定が適用されます。

SearchDirection(向きの反転)

' vba
'左から右、上から下(省略した場合も同様)
Set foundCell = Range("A1:A10"). _
Find(What:="3", SearchDirection:=xlNext)
'右から左、下から上
Set foundCell = Range("A1:A10"). _
Find(What:="3", SearchDirection:=xlPrevious)
引数SearchDirectionを変更すると、この「左から右」「上から下」を逆方向にすることができます。
値はXlSearchDirection列挙型定数で指定します。
xlNextで通常通り、XlPreviousで逆順(右→左、下→上)で検索が実行されます。
XlDirectionを省略した場合はxlNextになります。
searchdirection find vba
逆方向に検索する操作は通常の「検索と置換」にはなく、設定は保存されません。

MatchCase(大文字/小文字区別)

excel matchcase vba 1
引数MatchCaseは、検索画面の「大文字と小文字を区別する」にあたります。
チェックが外れてる(False)なら、Aaは同じ文字列としてヒットし、
excel matchcase vba 2
チェックが付いている(True)なら、違う文字列として扱われるのでヒットしません。
' vba
'Trueで大文字小文字を区別する、Falseでしない
Set foundCell = Range("B1:B10"). _
Find(What:="A", MatchCase:=False)
MsgBox foundCell
実装例です。

※MatchCaseも同様に、変更しなければ次のFindで同じ設定が適用されます。

MatchByte(半角/全角区別)

vba matchcase find 1
引数MatchByteは、検索画面でいうところの「半角と全角を区別する」にあたります。
チェックが外れてる(False)なら、テストテストは同じ文字列としてヒットし、
vba matchcase find 2
チェックが付いている(True)なら、違う文字列として扱われ、ヒットしません。
' vba
'Trueで全角半角を区別する、Falseでしない
Set foundCell = Range("B1:B10"). _
Find(What:="テスト", MatchByte:=False)
MsgBox foundCell
このコードだと、半角全角を区別しない状態で検索し、取得したセルの値=テストがメッセージボックスに表示されます。
なお、アルファベットや数字の全角/半角も判別可能です。

※MatchByteも同様に、変更しなければ次のFindで同じ設定が適用されます。

SearchFormat(書式検索)

excel vba searchformat1
書式検索は文字通り、文字列ではなく書式(背景色、罫線など)でセルを計算する機能です。Excel2002から追加されました。
excel vba searchformat2
たとえば背景色を設定し、検索を実行すると、文字列がない状態でもセルを特定することができます。
' vba
'Trueで書式検索有効、Falseで無効
Set foundCell = Range("A1:A10"). _
Find(What:="", SearchFormat:=True)
MsgBox foundCell
引数SearchFormatでは書式検索を行うかどうかだけを選択できます。検索する具体的な書式の内容は別の命令で行います。
Trueであれば書式検索が有効となり、文字列が指定されてれば書式+文字列が正しいセルのみがヒットします。
excel vba searchformat3
SearchFormatの設定は次回に保存されませんが、検索画面で設定した「検索する書式」は保存されます。
手動で書式を設定した後で上のコードを実行すると、ちゃんとヒットすることが確認できます。

FindFormat(検索する書式の設定)

' vba
'検索書式をクリア
Application.FindFormat.Clear
検索する書式の内容は、Application.FindFormatプロパティで設定することができます。
先述の通り設定した「検索する書式」は次回の検索にも持ち越されるので、最初に一回Clearをかけて、前の「検索する書式」の設定を削除しておくのが無難です。
' vba
Dim foundCell
'検索書式をクリア
Application.FindFormat.Clear
'検索書式(背景色)を設定
Application.FindFormat.Interior.ColorIndex = 6
'Trueで区別する、Falseでしない
Set foundCell = Range("A1:A10"). _
Find(What:="", SearchFormat:=True)
MsgBox foundCell
あとはフォントとかの設定と同じです。
たとえばFindFormatに対してInterior.ColorIndexをかければ、検索する書式の背景色を黄色に指定できます(なぜ6が黄色なのかはリンク先参照)。
エクセル excel findformat 1
その他の書式についてはあまり試してないですが、フォント罫線などは問題なく検索できます。たとえば罫線(実線)のあるセルを検索するなら、Application.FindFormat.Borders.LineStyle = xlContinuousのようになります。

※Clearしなければ、FindFormatの設定は次回の検索に持ち越されます。

FindNextメソッド(同じ条件で再度検索)

' vba
Dim foundCell
Dim foundCell2
Set foundCell = Range("A1:A10"). _
Find(What:="3")
'1回目の検索結果
MsgBox foundCell.Address
'同じ条件で続けて検索
Set foundCell2 = Range("A1:A10"). _
FindNext(After:=foundCell)
'2回目の検索結果
MsgBox foundCell2.Address
NextFindメソッドを使うと、Findの条件を引き継いで再度検索を行うことができます。
FindNextの引数はAfterのみ指定可能で、Whatなどを指定するとエラーになります。
なお、FindNextではSearchFormatを引き継ぐことはできません。書式で連続検索したい場合は、Findメソッドを複数回記述します。
findnext vba excel 1
上のコードを実行するとまず最初の「3」のセルのアドレスが表示され、
findnext vba excel 2
その後FindNextで同条件の検索がヒットしたセルの次のセルから再度行われ、今度は別の「3」のセルがヒットします。
Findメソッドを二回実行してもいいんですが、若干簡潔に書けます。
excel findnext 検索 一周
該当する検索結果すべてヒットさせたい場合、たとえば以下のようなコードになります。Do until Loopステートメントで条件付きのループ処理を行い、最初の検索結果と最後の検索結果が一致した(=一周した)時点で検索を終了します。
' vba
Dim foundCell
Dim firstCell
Set foundCell = Range("A1:A10"). _
Find(What:="3")
If foundCell Is Nothing Then ' 見つからない場合①
Else
MsgBox foundCell.Address
'最初の検索結果をfirstCellとして保存
Set firstCell = foundCell
'同じ条件で続けて検索(ループ)
Do
Set foundCell = Range("A1:A10"). _
FindNext(After:=foundCell)
If foundCell Is Nothing Then ' 見つからない場合②
Exit Do' ループ終了
End If ' 見つからない場合②の終わり
'最初の検索結果と現在の検索結果が同じ場合アドレスを表示しない
If foundCell.Address <> firstCell.Address Then
MsgBox foundCell.Address
End If
'最初の検索結果=現在の検索結果の場合ループ終了
Loop Until foundCell.Address = firstCell.Address
End If ' 見つからない場合①の終わり

Replaceメソッドの使い方

excel vba replace1
Findメソッドと引数がほぼ共通するので、一緒にReplaceメソッドも紹介します。
Replaceメソッドでは文字の置換を行うことができます。
excel vba replace2
記述的には、範囲.Replace 対象の文字列,置換後の文字列が基本形となります。
excel vba replace3
実行すると、対象の文字列がすべてまとめて置換されます。
excel vba replace5
つまり、通常操作で言うすべて置換が実行されます。
excel vba replace4
引数名で言えば、検索する文字列What置換後の文字列Replacementとなります。

その他の引数は以下の通り。Findメソッドと比較すると、After(開始箇所)、LookIn(数式、値、コメント)、SearchDirection(通常か逆順か)がなくなっています。
LookInがないので、「数式」でしか検索できません。これについては後述。

文字列を置換
検索範囲.Replace(What,Replacement,LookAt,
SearchOrder,MatchCase,MatchByte,SearchFormat,ReplaceFormat)

※What、Replacement以外は省略可
※LookAt、SearchOrder、MatchCase、MatchByteは
省略した場合、前回の設定を引き継ぐ
引数名 意味 値の保存
What 検索する文字列 文字列
Replacement 置換後の文字列 文字列
LookAt セル内容が完全に同一か XlPart(部分一致検索)、XlWhole(完全一致検索)
SearchOrder 検索方向 xlByRows(行方向検索)、xlByColumns(列方向検索)
MatchCase 大文字と小文字 True(区別する)、False(区別しない)
MatchByte 全角と半角 True(区別する)、False(区別しない)
SearchFormat 書式検索 True(する)、False(しない)
ReplaceFormat 書式置換 True(する)、False(しない)

ReplaceFormat

' vba
'検索/置換書式をクリア
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
'検索/置換書式(背景色)を設定
Application.FindFormat.Interior.ColorIndex = 6
Application.ReplaceFormat.Interior.ColorIndex = 7
ReplaceFormatプロパティFindFormatと同じく、頭にApplicationを付けて書式を指定します。
たとえばこういったコードを実行すると、
excel vba 置換 replaceformat
置換前と置換後の書式がセットされるので、
' vba
'置換実行
Range("A1:A10").Replace _
    What:="", _
    Replacement:="", _
    SearchFormat:=True, _
    ReplaceFormat:=True
あとは値を空欄にした(書式だけ変更する)Replaceメソッドを実行すれば、
excel vba 置換 replaceformat2
指定した書式だけを置換することが可能です。

Replaceでは「値」で検索できない

excel vba 置換 数式 値1
Excelの仕様として、置換には「数式」以外の検索対象がありません。なので、ReplaceでもLookInの指定はできなくなっています。
excel vba 置換 数式 値2
たとえばA1には「abc」、A2には「=”a”&”bc”」が入力された状態でabcをdefに置換しようとした場合、A2はひっかからず、置換もできません。
不便と言えば不便ですが、
excel vba 置換 数式 値3
もし値で部分検索の置換ができてしまうと、仮にabをdeに置換となった時に、A2を=”a”&”bc”から=”d”&”ec”にする(この関係性は無限に複雑にできる)か、式を壊さないといけなくなります。そのへんの兼ね合いなのかな、と思っています。
' vba
'置換実行
Range("A1:A2").Replace _
    What:="abc", _
    Replacement:="def"
式を壊してもいいのであれば対処は簡単です。
このabcをdefに置換する普通のコードの前に、
' vba
'値に変換
Range("A1:A2").Value = Range("A1:A2").Value
'置換実行
Range("A1:A2").Replace _
    What:="abc", _
    Replacement:="def"
範囲.Value = 範囲.Valueという式を追加します。
取得したValueプロパティを同じ場所に貼り付けしているので、このコードを実行した時点で=”a”&”bc”abcに変換されます。
excel vba 置換 数式 値4
従って普通に置換できますが、必要な式を壊す恐れがある場合は使わないか、ワンクッション置く形にアレンジしてください。
Excel(エクセル):検索、置換
検索・置換関連の操作、関数、VBAまとめです。 検索・置換操作 検索の基本 検索をするには、ホームタブの右端あたりにある、 検索と選択から検索をクリックします。 検索画面を起動 Ctrl+F またはショート...
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント