Excelで作業していると、こんな場面に遭遇しませんか？
- シート全体の「株式会社〇〇」を「〇〇株式会社」に変更したい
- 複数シートにまたがる商品名を一括で変更したい
- 特定の文字列が含まれるセルだけ処理したい
- 置換後に「何件変更したか」をログで残したい
手作業でCtrl+Hから置換するのも良いですが、毎回同じ置換をする作業はVBAで自動化できます。
今回紹介するコードを使えば、複数の置換ルールをボタン1つで実行できます。
こんな人におすすめ
- 定期的に同じ文字列の置換作業をしている人
- 複数シートに対して一括置換したい人
- 置換件数を記録・確認したい人
- Find・Replaceメソッドの使い方を学びたい初心者
このコードを覚えれば、置換作業の時間が圧倒的に短縮されます。
コードの動作イメージ
- 特定のシート、または全シートから文字列を検索
- 見つかったセルを自動で置換
- 置換した件数を画面に表示
- 複数の置換ルールを連続で実行
これらの作業をボタン1つで完了します。
基本：シート内の文字列を一括置換するVBAコード
Sub ReplaceTextBasic()
Dim ws As Worksheet
Dim searchText As String
Dim replaceText As String
Dim resultCount As Long
' 1. 対象シートを指定
Set ws = ThisWorkbook.Worksheets("Sheet1")
' 2. 置換する文字列を設定
searchText = "株式会社ABC"
replaceText = "ABC株式会社"
' 3. 一括置換を実行
With ws.Cells
resultCount = .Replace(What:=searchText, _
Replacement:=replaceText, _
LookAt:=xlPart, _
MatchCase:=False)
End With
' 4. 結果を表示
MsgBox resultCount & "件を置換しました。" & vbCrLf & _
"【" & searchText & "】→【" & replaceText & "】", vbInformation
End Sub
コードのポイント解説
① Replace メソッドで一括置換
resultCount = .Replace(What:=searchText, _
Replacement:=replaceText, _
LookAt:=xlPart, _
MatchCase:=False)
- What：検索する文字列
- Replacement：置換後の文字列
- LookAt：
xlPart（部分一致）またはxlWhole（完全一致） - MatchCase：
Falseで大文字小文字を区別しない
戻り値として置換した件数が返ってきます。
② LookAt の使い分け
LookAt:=xlPart ' 部分一致（セル内に含まれていればOK）
LookAt:=xlWhole ' 完全一致（セル全体が一致する場合のみ）
例：
xlPartの場合：「ABC商事株式会社」→「ABC商事株式会社」にも適用されるxlWholeの場合：セルの値が完全に「株式会社ABC」の場合のみ置換
応用編①：複数の置換ルールを連続実行
実務では、複数の文字列を一度に置換したいケースがよくあります。
Sub ReplaceMultipleText()
Dim ws As Worksheet
Dim replaceList As Variant
Dim i As Long
Dim totalCount As Long
Dim resultMsg As String
' 1. 対象シートを指定
Set ws = ThisWorkbook.Worksheets("Sheet1")
' 2. 置換リストを配列で定義（検索語, 置換語）
replaceList = Array( _
Array("株式会社ABC", "ABC株式会社"), _
Array("(株)", "株式会社"), _
Array("TEL:", "電話番号："), _
Array("Mail:", "メール：") _
)
' 3. 置換ルールを順に実行
totalCount = 0
resultMsg = "【置換結果】" & vbCrLf & vbCrLf
For i = 0 To UBound(replaceList)
Dim count As Long
count = ws.Cells.Replace(What:=replaceList(i)(0), _
Replacement:=replaceList(i)(1), _
LookAt:=xlPart, _
MatchCase:=False)
resultMsg = resultMsg & "【" & replaceList(i)(0) & "】→【" & replaceList(i)(1) & "】：" & count & "件" & vbCrLf
totalCount = totalCount + count
Next i
' 4. 結果を表示
resultMsg = resultMsg & vbCrLf & "合計：" & totalCount & "件を置換しました。"
MsgBox resultMsg, vbInformation
End Sub
このコードを使えば、複数の置換ルールを一発で実行できます。
応用編②：全シートに対して一括置換
ブック内の全シートに対して置換を実行する場合は、次のコードが使えます。
Sub ReplaceAllSheets()
Dim ws As Worksheet
Dim searchText As String
Dim replaceText As String
Dim totalCount As Long
Dim sheetMsg As String
' 1. 置換する文字列を設定
searchText = "旧社名"
replaceText = "新社名"
' 2. 全シートをループ
totalCount = 0
sheetMsg = "【シート別置換結果】" & vbCrLf & vbCrLf
For Each ws In ThisWorkbook.Worksheets
Dim count As Long
count = ws.Cells.Replace(What:=searchText, _
Replacement:=replaceText, _
LookAt:=xlPart, _
MatchCase:=False)
If count > 0 Then
sheetMsg = sheetMsg & ws.Name & "：" & count & "件" & vbCrLf
totalCount = totalCount + count
End If
Next ws
' 3. 結果を表示
If totalCount > 0 Then
sheetMsg = sheetMsg & vbCrLf & "合計：" & totalCount & "件を置換しました。"
MsgBox sheetMsg, vbInformation
Else
MsgBox "該当する文字列が見つかりませんでした。", vbInformation
End If
End Sub
応用編③：特定の列だけを対象に置換
「A列だけ」「見出し行を除く範囲だけ」など、範囲を限定したい場合のコードです。
Sub ReplaceInSpecificRange()
Dim ws As Worksheet
Dim targetRange As Range
Dim searchText As String
Dim replaceText As String
Dim count As Long
' 1. 対象シートと範囲を指定
Set ws = ThisWorkbook.Worksheets("Sheet1")
' A列の2行目以降を対象にする（見出し行を除く）
Set targetRange = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
' 2. 置換する文字列を設定
searchText = "仮"
replaceText = "確定"
' 3. 指定範囲内で置換実行
count = targetRange.Replace(What:=searchText, _
Replacement:=replaceText, _
LookAt:=xlPart, _
MatchCase:=False)
' 4. 結果を表示
MsgBox "A列の2行目以降で" & count & "件を置換しました。", vbInformation
End Sub
Find メソッドで1件ずつ処理する方法
Replace は一括置換ですが、Find を使えば見つかったセルに対して個別の処理ができます。
Sub FindAndProcessEachCell()
Dim ws As Worksheet
Dim searchText As String
Dim foundCell As Range
Dim firstAddress As String
Dim count As Long
' 1. 対象シートと検索文字列を設定
Set ws = ThisWorkbook.Worksheets("Sheet1")
searchText = "重要"
' 2. 最初のセルを検索
Set foundCell = ws.Cells.Find(What:=searchText, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
' 3. 見つかった場合の処理
If Not foundCell Is Nothing Then
firstAddress = foundCell.Address
count = 0
Do
' 見つかったセルに対して処理（例：背景色を黄色に）
foundCell.Interior.Color = RGB(255, 255, 0)
foundCell.Font.Bold = True
count = count + 1
' 次のセルを検索
Set foundCell = ws.Cells.FindNext(foundCell)
Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddress
MsgBox "【" & searchText & "】が含まれるセル " & count & "件に色を付けました。", vbInformation
Else
MsgBox "該当する文字列が見つかりませんでした。", vbInformation
End If
End Sub
このコードは、検索でヒットしたセルすべてに書式を設定するときに便利です。
よくあるエラーと対処法
エラー①：置換件数が思ったより少ない
原因： セルの書式や非表示文字が原因で一致しないことがあります。
対処法：
' LookIn パラメータを追加
.Replace What:=searchText, _
Replacement:=replaceText, _
LookIn:=xlValues ' xlFormulas（数式）または xlValues（値）
エラー②：数式内の文字列まで置換されてしまう
原因： デフォルトでは数式内も置換対象になります。
対処法：
LookIn:=xlValues ' 値のみを対象にする
エラー③：大文字・小文字を区別したい
対処法：
MatchCase:=True ' 大文字小文字を区別する
実務での活用例
✅ 定期的な社名変更・部署名変更の一括処理
✅ システムから出力したデータの表記統一
✅ テンプレートの「○○株式会社」を実際の取引先名に一括変換
✅ 郵便番号の表記統一（「123-4567」→「〒123-4567」）
✅ 特定キーワードを含むセルに自動で色を付ける
まとめ：置換作業はVBAで自動化しよう
- Replace メソッドで一括置換が簡単にできる
- LookAt パラメータで部分一致・完全一致を使い分け
- 複数の置換ルールを配列で管理すれば連続実行も可能
- Find メソッドを使えば見つかったセルごとに個別処理ができる
- 全シート・特定範囲など、対象を柔軟に指定できる
毎回手作業で置換している作業があれば、ぜひVBAで自動化してみてください。
コメント