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.
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 capitalization 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.
VBA Search & Word Replacement
Visual Basic for Applications (VBA) is a programming language built into Microsoft Office's Word, Excel and Access programs. Users of these programs...
How to Create an Input Box in Excel VBA
An input box in Visual Basic lets you prompt the user for some input and store the data entered into a variable....
How to Use VBA Codes in Excel
Visual Basic for Applications (VBA) is a programming language you can use to tailor Microsoft Office applications to fit your specific needs....
How to Change the Source of an Excel Pivot Table Using VBA
Microsoft Excel is a spreadsheet application. One feature of this program is that you can use its worksheets to store data similarly...
How Do I Find & Replace Special Characters in Excel 2007?
One of the nicest functions in Microsoft Excel to quickly change cells is the Find and Replace function. This function allows you...
How to Use Excel VBA for Printing
A Visual Basic Application (VBA) procedure is used to automate routine tasks in Microsoft Excel in order to save time. The VBA...
How to Use a Do-While Loop in Excel VBA
When using Excel's VBA, or visual basic editor, create a code that runs when a worksheet is activated and use a do-while...
VBA Tutorial for Word
Visual Basic for Applications (VBA) is a programming language used to automate tasks in Microsoft Word. With Visual Basic you can do...