How to Find and Replace Values in Cell Using Excel Macro

Excel files may contain frequent cell values that are no longer valid or are entered incorrectly in the first place. After rectification, those cell contents need to be replaced with the correct ones. You can do this task using default find and replace all facilities using the Ctrl+ H shortcut.

But, in case you want to do it using VBA macro code, you are here at the right place. This option will give you more control and customization options. You can replace multiple words in a single click using this macro.

In this article, we will explore how to create an Excel macro using VBA that finds and replaces specific values within a worksheet or workbook. Additionally, I will show you how to display an alert to indicate how many cells have been changed during the process.

Steps to create find and replace Excel macro

To get started, open Excel and press “Alt + F11” to open the Visual Basic Editor. Then, follow the steps below:

Step 1: Create a new macro

In the Visual Basic Editor, click on “Insert” in the menu bar, and then select “Module” to create a new module. This is where we’ll write our VBA code.

Step 2: Write the VBA code

In the module, write the following find and replace cell value VBA Excel macro code:

Sub FindAndReplace()
    Dim oldValue As String
    Dim newValue As String
    Dim totalChanges As Long

    ' Set the values you want to find and replace
    oldValue = "old value"
    newValue = "new value"

    ' Disable screen updating for faster execution
    Application.ScreenUpdating = False

    ' Loop through all worksheets in the workbook
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ' Use the built-in Find function to find the old value
        With ws.Cells
            Dim rngFound As Range
            Set rngFound = .Find(What:=oldValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

            ' Check if the old value is found
            If Not rngFound Is Nothing Then
                ' Loop through all cells that contain the old value
                Do
                    ' Replace the old value with the new value
                    rngFound.Value = newValue

                    ' Move to the next cell containing the old value
                    Set rngFound = .FindNext(rngFound)

                    ' Increment the total changes counter
                    totalChanges = totalChanges + 1
                Loop While Not rngFound Is Nothing
            End If
        End With
    Next ws

    ' Enable screen updating
    Application.ScreenUpdating = True

    ' Show an alert with the total number of changes made
    MsgBox totalChanges & " cell(s) have been changed.", vbInformation
End Sub

Step 3: Customize the code

In the code above, you’ll notice the variables oldValue and newValue. Replace “old value” with the value you want to find and “new value” with the value you want to replace it with. You can modify the code further to meet your specific requirements, such as changing the search parameters or applying the find and replace operation to a specific range.

Step 4: Run the macro

To run the macro, close the Visual Basic Editor and return to Excel. Press “Alt + F8” to open the Macro dialog box. Select the “FindAndReplace” macro and click on the “Run” button. The macro will start executing, finding, and replacing the specified values in all worksheets within the workbook. Once completed, an alert box will display the total number of changes made.

By utilizing this Excel macro, you can save valuable time and effort, especially when dealing with large datasets or recurring find-and-replace tasks. VBA empowers users to automate repetitive operations, making Excel an even more efficient tool for data manipulation and analysis.

In this way, you can easily create and execute VBA macro to find and replace specific words. Go ahead and explore customizing this script. You can unleash more potential with it.