Replace（リプレイス）は「ある文字列を別の文字列に置き換えたい時」に使います。
ReplaceメソッドとReplace関数の違い
ある文字列をを別の文字列に置き換えるというザックリした表現で言えば同じですが、使い勝手が異なります。
(*’ω’*)σ それぞれのメリット・デメリットがあるので、のちほど比較しながら解説します。
| Replaceメソッド | Replace関数 |
|---|---|
| セル範囲の中から指定の文字列を置き換える | 文字列の中から指定の文字列を置き換える |
| 検索で言うとFindメソッドにあたる | 検索で言うとInStr関数にあたる |
▼ Replaeメソッドのメリット・デメリット
- メリット
🔹ワイルドカード「*」が使用できる
🔹オブジェクトをセル範囲にすれば複数のセルに一気に実行できる - デメリット
🔹セルがそのまま置き換わるので元データを残すのは不可
🔹ワークシート「検索と置換」ダイアログの影響を受けてしまう
🔹オートフィルターで非表示になっているセルは感知できない
▼ Replace関数のメリット・デメリット
- メリット
🔹関数なので変数やシート名なども置換できる
🔹置き換えた結果を別の場所に出力したりもできる - デメリット
🔹複数のセルに実行するにはFor…Nextなどループ処理が必要
Replaceメソッドの使い方・使用例
指定したセル範囲内から検索した文字列を他の文字列に置換する場合に使用します。
▼ Peplaceメソッドで使用する引数の一覧
| 引数 | 用途 | 設定値 | 内容 |
|---|---|---|---|
| What | 検索する文字を指定 | 任意の文字列 | |
| Replacement | 置き換える文字を指定 | 任意の文字列 | |
| LookAt | 検索条件を指定 | xlWhole | 完全一致 |
| 〃 | 〃 | xlPart | 部分一致 |
| SearchOrder | 検索の方向を指定 | xlByRows | 行方向 |
| 〃 | 〃 | xlByColumns | 列方向 |
| MatchCase | 大文字と小文字を区別して検索するか？ | True,False | 区別する→True 区別しない→False |
| MatchByte | 全角と半角を区別して検索するか？ | True,False | 区別する→True 区別しない→False |
| SearchFormat | 検索するセルの書式を指定するか？ | True,False | 区別する→True 区別しない→False |
| ReplaceFormat | 置換するセルの書式を指定するか？ | True,False | 区別する→True 区別しない→False |
完全一致する場合に置換を実行する
仕入担当の小林さんが退職して木下さんが引き継ぐので「小林→木下」の置換を行います。
使用例 小林さんを木下さんに入れ替え
Sub 小林さん退職()
Range(“A4:F13″).Replace What:=”小林”, Replacement:=”木下”, LookAt:=xlWhole
End Sub
セルA4からF13の範囲で「小林」と完全一致する文字列を「木下」に置換するという内容です。
完全一致なのでLookAtの設定値はxlWholeです。実行すると下図のように置き換わります。
部分一致する場合に置換を実行する
使用例 「ベリー」が付くいちごを「○○ベリー☆」に置換+書式を設定
Sub なんたらベリー()
With Application.ReplaceFormat ‘置換後の書式を設定
.Font.Bold = True ‘太字にする
.Interior.ColorIndex = 22 ‘セルをコーラルピンクに塗りつぶす
End With
Range(“A4:F13″).Replace What:=”ベリー”, Replacement:=”ベリー☆”, LookAt:=xlPart, _ SearchFormat:=False, ReplaceFormat:=True
‘セルA4からF13の範囲で「ベリー」の部分を「ベリー☆」にしてセルに書式を設定する
‘置換前のセルの書式は特に指定無し
End Sub
まずWithでまとめて書式の処理内容、次に置換の処理内容、そして書式の設定という流れです。実行すると下図のようになります。
曖昧一致する場合に置換を実行する（ワイルドカード）
3兄弟で仕入れをしていた伊藤さんが、伊藤商会を立ち上げました。3人を「伊藤商会」に置き換えます。
使用例 伊藤さんたちを「伊藤商会」に置換
Sub 伊藤商会発足()
With Application.ReplaceFormat ‘置換後の書式を設定
.Font.Bold = True ‘太字にする
.Interior.ColorIndex = 22 ‘セルをコーラルピンクに塗りつぶす
End With
Range(“A4:F13″).Replace What:=”伊藤*”, Replacement:=”伊藤商会”, LookAt:=xlPart, _ SearchFormat:=False, ReplaceFormat:=True
‘セルA4からF13の範囲で「伊藤」を含む文字列を「伊藤商会」にしてセルに書式を設定する
‘置換前のセルの書式は特に指定無し
End Sub
実行すると下図のようになります。
Replace関数の使い方・使用例
Replaceメソッドではセルを対象に置換を行いましたが、Replace関数では任意の文字列を対象に置換を行います。
基本構造
Replace（Expression,Find,Replace,Start,Count,Compare）
引数を日本語で書くと・・・
Replace(対象文字列,検索文字列,置換文字列,開始位置,置換回数,比較モード）
開始位置・置換回数・比較モードは省略可能
▼ Replace関数で使用する引数の一覧
| 引数 | 内容 |
|---|---|
| Expression(対象文字列） | 置換対象となる元の文字列を指定する |
| Find(検索文字列) | 対象文字列内で検索される文字列を指定する |
| Replace(置換文字列) | 検索文字列と置き換える文字列を指定する |
| Start(開始位置） | 検索を開始する位置を指定 ※省略時は1 |
| Count(置換回数） | 置換する回数を指定 ※省略時は全て置換 |
| Compare (比較モード) | ・vbBinaryCompare（規定値） 「大文字･小文字」「半角･全角」「ひらがな･カタカナ」を区別して検索 ・vbTextCompare 「大文字･小文字」「半角･全角」「ひらがな･カタカナ」を区別しない |
繰り返し処理を用いて複数の文字列を置換
先程Replaceメソッドで行った「小林さん退職して木下さんに交代」をReplace関数で行ってみます。同じ処理をする場合でプロシージャを比べてみます。
使用例 「小林」を「木下」に置換
Sub 小林さん退職2()
Dim i As Long
For i = 4 To 13
If Cells(i, 6) = “小林” Then
Cells(i, 6) = Replace(Cells(i, 6), “小林”, “木下”)
End If
Next i
End Sub
この場合だと、複数のセルを一括処理できるReplaceメソッドの方が簡単に使えますね。
数式を置換
合計値を求める数式を、平均値を求める数式に置換します。
セルD14に販売数量の合計を求める数式「=SUM(D4:D13)」が入っています。
使用例 合計値を求める数式を、平均値を求める数式に置換
Sub 合計値から平均値()
Range(“D14”) = Replace(Range(“D14”).Formula, “SUM(“, “SUBTOTAL(101,”)
End Sub