Find And Replace All With Excel VBA

Hi Excellers and welcome back for another #MacroMonday in my 2020 series. I use this small find and replace Excel macro on a regular basis. It will replace values or sales value with 1. Any zeros are left, creating a binary numbering file. So, any values greater than zero are placed with 1. Zeros are left. Let’s get coding!

Preparing To Write The Code.

First, you will need to open the Visual Basic Editor. There are two ways to do this.

Either by hitting ALT +F11

Selecting the Developer Tab | Code Group | Visual Basic.

Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save you code in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If the macro is stored in the current workbook then use is restricted to that workbook. This code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro Workbook.

Learn More About Your Personal Macro Workbook (PMW)

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook Macro Mondays

Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply ReplaceNumbers. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]

Sub ReplaceNumbers ()

[/stextbox]

Declaring Variables.

The first piece of code we need to write is a DIM statement. We need to declare two variables. The declaration of variables simply creates a memory container for these values in Excel. I am declaring in this macro the that the user user selected cells is the range of cells to replace the values in. [stextbox id=’info’] Dim rng As Range Set rng = Selection [/stextbox]

Use The For Next Loop To Find And Replace.

Next, the For Next Loop method is use to loop through each of the cells in the user selected range. Any cells that contain values over zero with be replaced with 1. Excel will loop through all of the cells,. Excel will find and replace any values that are greater than zero. The looping ceases once all cells in the range has been looped. [stextbox id=’info’] For Each cell In rng If cell.Value > 0 Then cell.Value = 1 End If Next [/stextbox]

Ending The Macro.

The routine then ends with the End Sub piece of code. This is already entered into the module for us when started the type the name of the Macro. [stextbox id=’info’] End Sub [/stextbox]

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below