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
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.