Excel VBA 一括置換マクロ(コピペで使える便利機能)

 

日常業務を行うにあたって、 エクセル内のある文字列を置換する必要がある、しかも様々な値を大量に。。。 という経験はないでしょうか。

 

そんなときファイル内の文字列などを一括で置換することができたら良いなと思ったことはないでしょうか。

 

そこで、エクセルの表で検索文字列と置換する文字列をいくつか指定して実行すると指定した全ての文字列を一括で置換してくれるマクロを作成しましたのでご活用頂けたらと思います。

 

一括置換マクロの概要

エクセルの表内に検索する文字列置換する文字列を記載した状態で、対象のファイルをダイアログで指定すると、そのファイル内の全てのシートを対象に検索する文字列を置換する文字列に変換してくれます。

 

ExcelにてVBAコードよりマクロを作成し、ボタン(図形にて作成)をクリックすることでマクロを実行させて一括置換処理を実施します。

 

何ができるマクロなのか?

1.一括置換マクロのVBAコードを含んだExcelファイル(***.xlsm)を作成して、そのExcelファイルを開きます。

 

2.開いたExcelファイルの表にどの文字列を検索対象(A列)として、その値をどの文字列に置換する(B列)かを記入します。

 

3.検索する文字列と置換する文字列を記入した後に、図形ボタンを押して一括置換する対象のファイルを選択します。

 

4.3で指定したファイル内にある指定した検索文字が置換文字に変換されています。

一括置換マクロの使用方法

1.Excelを開き、「開発」タブをクリックして、「Visial Basic」をクリックします。

 

2.「Microsoft Excel Object」にて右クリックして、「挿入」→「標準モジュール」をクリックします。

 

3.右側の空欄スペースに下記のVBAコードをコピペで入力します。

Option Explicit

Sub replace_all()

    On Error GoTo err
        
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim filePath As String
    Dim fileName As String
    Dim tgtSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim baseSheet As Worksheet
    Set baseSheet = ActiveSheet
    Dim searchWord As Variant
    Dim replaceWord As Variant
    Dim msg As String
    
    'リストの最終行を検索
    lastRow = baseSheet.Cells(Rows.Count, "A").End(xlUp).Row

    If lastRow < 3 Then
        MsgBox "検索する文字列を設定してください。"
        End
    End If

    'ダイアログの表示処理
    filePath = Application.GetOpenFilename(Filefilter:="Microsoft Excelブック,*.xls?,csvファイル,*.csv", Title:="一括置換する対象のファイルを選択してください。")

    If filePath <> "False" Then
        
        'ファイル名のみを取得する
        fileName = Dir(filePath)
        Workbooks.Open (filePath), UpdateLinks:=1
        
        '対象ファイルの全シートを1つずつループして処理する
        For Each tgtSheet In ActiveWorkbook.Worksheets
            
            'リストの分だけ置換を繰り返す
            For i = 3 To lastRow
                
                searchWord = baseSheet.Cells(i, 1).Value
                replaceWord = baseSheet.Cells(i, 2).Value
                
                Select Case searchWord
                    Case "^", "$", "?", "*", "+", ".", "|", "{", "}", "\", "[", "]", "(", ")"
                    searchWord = "~" & searchWord
                End Select
                    
                Dim Rng As Range
                Set Rng = tgtSheet.Cells.Find(What:=searchWord, _
                                        LookAt:=xlPart, _
                                        SearchFormat:=True)
                                        
                If Rng Is Nothing And InStr(msg, baseSheet.Cells(i, 1).Value) = 0 Then
                    msg = msg & "「" & baseSheet.Cells(i, 1).Value & "」がファイル内に見つかりませんでした。" & vbCr
                Else
                    tgtSheet.Cells.Replace What:=searchWord, _
                                            Replacement:=replaceWord, _
                                            LookAt:=xlPart, _
                                            SearchFormat:=True, _
                                            ReplaceFormat:=True
                End If
            Next
        Next
        
        Workbooks(fileName).Close SaveChanges:=True
        
        If msg = "" Then
            MsgBox "一括置換処理が正常に終了しました。"
        Else
            MsgBox msg
        End If
        
    'キャンセルが選択された場合はダイアログを閉じる
    Else
        End
    End If
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    Exit Sub
err:
    MsgBox "予期せぬエラーが発生しました。"
    
End Sub

 

4.ファイルを保存する際は「Excelマクロ有効ブック」を選択して、保存します。

 

5.Excelシートに戻り、3行目から検索文字列と置換する文字列を記入します。

(1行目は説明行、2行目はタイトル行として扱っているため、1、2行目の値は無視されます)

 

6.上部メニューの「挿入」タブ→「図形」→「四角形」を選択して、シート内に図形(四角形以外でも良いです)を作成します。

 6は当記事の一番下のエクセルファイルをダウンロードして使用する場合は不要です。

 

7.6で作成した図形にメイン処理「replace_all」のマクロを設定します。

 

8.7でマクロ登録した図形にカーソルを当てて、指の形になっているのを確認してクリックします。

 

このマクロを使う上での注意点

当マクロを使用するにあたっての注意点がありますので、以下を確認してください。

ワイルドカードは使用できるが、正規表現を使用できない

正規表現である . ^ $ [ ] * + ? | ( ) は正規表現としては使用できず、ただの文字として認識され検索されます。

ワイルドカード * ? はワイルドカードとして使用できます。しかし一文字だけで記入した場合はワイルドカードとして認識せず、「*」の文字そのものとして検索されます。

検索文字が対象ファイル内にない場合にその旨をメッセージ表示

上記の例の検索文字を入れて実行した場合に、下2行分(6行目と7行目)が対象ファイル内にないため、その検索文字が見つからない旨がメッセージに表示されます。

 

なお、当マクロの開発環境として、OSは “Windows10” 、Excelのバージョンは “Microsoft Office 365 バージョン2006″ で行っており、当環境では動作確認ができていますが、他のすべての環境で正常に動作するかは確認できていません。

 

正常に動作しない場合は、コメントいただければ幸いです。

 

 

また、このようなExcelVBAを用いた業務効率化を行うときに、以下の書籍が初心者向けでとても参考になると思いますので、良かったらご参照ください。

 

サンプルのダウンロードはこちら

 一括置換マクロのツール本体

上記よりダウンロードして、VBAコードを組み込んでマクロを使用してください。
※マクロをインターネットからダウンロードするのはリスクを伴うと思いますので、マクロ入りでなくエクセル形式でアップロードしています。

 

Follow me!

コメントを残す

CAPTCHA