[VBA] How to Replace Cell Values | Loop vs. Replace Method

Overview

When correcting data in Excel, you often encounter situations where you want to “replace a specific string with another.” While it is possible to search and replace manually, using VBA allows you to automate the replacement of multiple cells at once.

This article introduces two methods for replacing cell values (Loop processing and the Replace method). You can choose the method that best suits your needs.

Sample Code

Sub ReplaceCityNames()

    ' 方法①：選択範囲内を1つずつ確認して置換
    Dim targetCell As Range
    For Each targetCell In Selection
        If targetCell.Value = "北町区" Then
            targetCell.Value = "南町区"
        End If
    Next targetCell

    ' 方法②：範囲を一括で置換（表示形式も維持）
    Range("B2:B10").Replace _
        What:="北町区", _
        Replacement:="南町区", _
        MatchByte:=False

End Sub

Code Explanation

Method 1: Replace One by One Using a Loop

For Each targetCell In Selection
    If targetCell.Value = "北町区" Then
        targetCell.Value = "南町区"
    End If
Next targetCell

• This checks the currently selected cells. It looks for cells that are exactly equal to “北町区” (Kitamachi-ku) and changes the value to “南町区” (Minamimachi-ku).
• It only targets cells where the case (uppercase/lowercase) and width (full-width/half-width) match exactly.
• This is suitable when you want to control the data conditions in detail.

Method 2: Batch Replacement Using the Replace Method

Range("B2:B10").Replace _
    What:="北町区", _
    Replacement:="南町区", _
    MatchByte:=False

• This replaces all instances of “北町区” with “南町区” within the range Range(“B2:B10”).
• MatchByte:=False ensures that the check ignores the difference between full-width and half-width characters.
• This is convenient when you want to replace values in a whole table or a large range at once.

Use Cases

• Batch updating master data such as company names or person names.
• Converting old notations to the latest naming conventions.
• Standardizing notations for place names or product names.

Notes

1. Partial Matches: The Replace method may replace partial matches. For example, replacing “City” might unintentionally change “New York City” to something else if not careful. You can specify LookAt:=xlWhole to force an exact match.
2. Formulas: Replacement might not apply to cells containing formulas in some cases. You can specify LookIn:=xlValues to handle this.

Summary

In this article, I introduced two approaches to replacing cell values in VBA.

By deciding which method to use based on your specific needs, you can significantly improve the efficiency of your Excel tasks.