【EXCEL VBA】複数条件の置換を一括で行う方法！3つ以上指定可！

Excelで複数条件を指定し一括置換したいときはないでしょうか。

けど、そんな中で悩むことは、

・Excelで複数条件を指定し一括置換したいが方法がわからない。
・置換関数で複数の条件を指定すると複雑になるので別の方法で行いたいがわからない。
・VBAでExcelで複数条件を指定し一括置換したいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する

Excelで複数条件の置換を一括で行う方法について

まとめます！

VBAで複数条件を指定し一括置換するイメージ

VBAで複数条件を指定し一括置換するイメージについて説明をします。

一括置換したい文字列を複数用意します。

置換の条件を複数入力したシートを追加します。

複数条件を指定し一括置換するVBAを用意します。

VBAを実行すると、

はい、一括置換ができました。

1セルに置換対象文字列が2語以上あってもまとめて置換されます。

↓置換後

別シートの文字列もカンタンな設定でサクッと置換ができます。

↓置換後

それでは早速使ってみましょう。

一括置換の対象となる文字列を入力したシートを用意する

一括置換の対象となる文字列を入力したシートを用意しましょう。

シート名は「置換対象」とし、A1からA6セルまでデータを入力していきます。

サンプルは以下のようにしました。

一括置換の条件を複数入力したシートを用意する

一括置換の対象となる文字列を入力したシートを用意しましょう。

シート名は「複数条件」とし、A1からA6セルまでに置換対象文字列、B1からB6まで置換後の文字列を入力していきます。

サンプルは以下のようにしました。

Excelで複数条件を指定し一括置換するVBA

続いて、Excelで複数条件を指定し一括置換するVBAについて説明をします。

VBAコード

ReplaceメソッドでExcelで複数条件を指定し一括置換するVBAを準備します。

サンプルコードは以下の通りです。

Sub 複数条件で一括置換する()

    Dim objRepRange As Object
    Dim objParagraph As Object
    Dim arrRepWords As Variant

    
    '置換範囲を設定します。
    Set objRepRange = Range("A1:A6") 'Range指定した置換対象となります。
    'Set objRepRange = Sheets("別シートの置換対象").Range("A1:A6") '別シートのRange指定が置換対象となります。
    'Set objRegRange = Selection 'マウスやキーボード操作で選択した範囲が置換対象となります。
    
    
    '置換前と置換後の文字が記載されている範囲を指定します。
    arrRepWords = Sheets("複数条件").Range("A1:B7")
    
    
    '置換対象文字列がある分処理を繰り返します。
    For Each objParagraph In objRepRange
    
        '複数条件の数分処理を繰り返します。
        For i = LBound(arrRepWords, 1) To UBound(arrRepWords, 1)
              
            '置換対象文字列に対し、変換後の文字列と変換前の文字列を置き換えます。
             objParagraph.Value = Replace(objParagraph.Value, arrRepWords(i, 1), arrRepWords(i, 2))
              
        Next
    Next
    
    'オブジェクトを解放します。
    Set objRepRange = Nothing
    Set objParagraph = Nothing

End Sub

VBAの設定をする

Excelで複数条件を指定し一括置換するVBAの設定を行っていきましょう。

設定箇所は以下の通りです。

置換範囲を設定します。サンプルでは「A1:A6」を指定しています。

Set objRepRange = Range(“A1:A6”)

マウスやキーボード操作で選択した範囲を置換対象としたい場合はこちらを有効化してください。

Set objRegRange = Selection

置換前と置換後の文字が記載されている範囲を指定します。サンプルでは「複数条件」シートの「A1:B6」を指定しています。

arrRepWords = Sheets(“複数条件”).Range(“A1:B6”)

VBAを実装し実行する

VBAを実装し実行してみましょう。

実装方法は「VBAの実装手順」をご覧ください。

実装できたらマクロから「複数条件で一括置換する」を見つけます。

マクロを実行します。

はい、置換対象範囲の文字列が置換されていますね！

置換前

↓置換後

VBAの説明

Excelで複数条件を指定し一括置換するVBAについて説明をします。

置換対象文字列がある分処理を繰り返します。

For Each objParagraph In objRepRange

複数条件の数分処理を繰り返します。

For i = LBound(arrRepWords, 1) To UBound(arrRepWords, 1)

Replaceメソッドで変換後の文字列と変換前の文字列を置き換えます。

objParagraph.Value = Replace(objParagraph.Value, arrRepWords(i, 1), arrRepWords(i, 2))

Replaceメソッドの式は以下の通りです。

Replace(元の文字列,検索する文字列,置換文字)

別シートの文字列の複数条件を指定し一括置換

続いて、これまで今見ているアクティブシートの範囲を置換処理の対象としてきましたが、別シートにある文字列を置換対象にするよう変更をしてみましょう。

シートを追加します。サンプルでは「別シートの置換対象」、文字列の範囲はRange(“A1:A6”)とします。

以下のコードをアクティブにします。

Set objRepRange = Sheets(“別シートの置換対象”).Range(“A1:A6”)

実行してみましょう。

はい、別シートの指定範囲が置換されていますね。

置換の条件を追加する

続いて、置換の条件を追加してみましょう。

シートは元に戻りまして、「置換対象」を選択します。

各文字列に「ジュース」を追加してみます。

現段階で置換をすると、当然ながら「ジュース」は置換条件にありませんので、以下のようになります。

文字列に「ジュース」を加えて、その「ジュース」を「juice」に置換するようにしていきます。

「複数条件」の7行目に「ジュース」、「juice」を入力します。

置換前と置換後の文字が記載されている範囲を指定します。「A1:B6」から「A1:B7」へ変更をします。

arrRepWords = Sheets(“複数条件”).Range(“A1:B7”)

実行してみましょう。

はい、「ジュース」が置換されていますね。

＜追加＞複数条件の置換を一括で行うVBAの改良/機能拡張版（Ver2.0）

複数条件の置換を一括で行うVBAの改良/機能拡張版（Ver2.0）を追加いたします。

前バージョンとの違い

上記のバージョンとの違いは以下の通りです。

・「置換対象」シートのA列以外にB列やC列など入力した最終列を判定し最終列の文字列を置換対象にするよう変更

・「置換対象」シートの最終行を判定し、置換範囲を自動設定するよう変更

・「複数条件」シートの最終行を判定し、置換条件設定の範囲を自動設定するよう変更

大きな変更点は
置換対象がA列のみだったのをA列以外も置換対象になるよう拡張した点です。
これにより表データも一括置換が可能になります。

これが

こうなります。

また、VBAコード内にある置換対象範囲の設定変更を行わなくてよいので、コードコピペですぐ使えるようにしています。

タカヒロ

忙しいビジネスパーソンにとってコピペ実装はうれしいですね。

サンプルコード

改良版サンプルコードは以下となります。

Sub 複数条件で一括置換する_V2()

    Dim objRepRange As Range
    Dim objParagraph As Range
    Dim arrRepWords As Variant
    Dim wsTarget As Worksheet
    Dim wsMulti As Worksheet
    Dim lTargetCols As Long
    Dim lTargetRows As Long
    Dim lMultiRows As Long
    Dim i As Long

    ' 対象シートを設定します。
    Set wsTarget = Sheets("置換対象")
    Set wsMulti = Sheets("複数条件")

    ' 最終列、最終行を算出します。
    lTargetCols = wsTarget.Cells(1, wsTarget.Columns.Count).End(xlToLeft).Column
    lTargetRows = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row
    lMultiRows = wsMulti.Cells(wsMulti.Rows.Count, 1).End(xlUp).Row

    '置換範囲を設定します。
    Set objRepRange = wsTarget.Range(wsTarget.Cells(1, 1), wsTarget.Cells(lTargetRows, lTargetCols))

    '置換する文字列范?を設定します。
    arrRepWords = wsMulti.Range("A1:B" & lMultiRows)

    'objRepRangeは範囲指定できますが、全セルに対して操作を行うため、各セルごとに参照します。
    For Each objParagraph In objRepRange

        '複数条件の数だけ処理を繰り返します。
        For i = LBound(arrRepWords, 1) To UBound(arrRepWords, 1)

            '設定した複数条件に合致するか検証し、合致する場合は文字列を置換します。
            objParagraph.Value = Replace(objParagraph.Value, arrRepWords(i, 1), arrRepWords(i, 2))

        Next
    Next

    'オブジェクトへの参照を解放します。
    Set objRepRange = Nothing
    Set objParagraph = Nothing
    Set wsTarget = Nothing
    Set wsMulti = Nothing

End Sub