When you need to automate tasks in Microsoft Excel 2010, you can turn to Visual Basic for Applications, a programming language that is included with all modern versions of Microsoft Office programs for Windows (VBA support was removed for Mac Office in version 2008 but replaced in version 2011). VBA allows you to run a small program over a number of Excel cells, unlike Excel formulas, which only work on the cell they are in. While the Excel program comes with a find and replace wizard, you can use VBA to find and replace cell contents without requiring any user input, and you can do so across all worksheets in the workbook.
- Skill level:
- Moderately Challenging
Other People Are Reading
Open the Excel workbook where you want to enter your VBA code. Press "Alt" and "F11" to open up the VBA console.
Double-click on the module where you want to enter your code. If there is no module you can right-click on a worksheet, move your mouse over "Insert" and choose "Module." Then double-click on the module to bring it up.
Copy the following formula into the module:
Dim sb As Worksheet
For Each sb In Worksheets
sb.Cells.Replace What:="XXX", Replacement:="YYY", LookAt:=xlPart, _
Change "XXX" to the value you are searching for and "YYY" to the value you want to replace it with. You can also change the "MatchCase" value to "True" if capitalisation is important. This macro will find and replace all desired values on every worksheet in your workbook.
Use the following code if you only want to replace all the values in a given selection:
Selection.Replace What:="XXX", Replacement:="YYY", LookAt:=xlPart, _
You can use this code within any other VBA macro as long as you enter this code after you have defined a selection area in your macro.
Click on the "X" in the top-right corner of the VBA window to close it. All your changes are automatically saved.
Click the "Developer" tab and press the "Macro" button. Choose your macro from the list and click "Run" to replace all the targeted values in your worksheet or workbook.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for