I usually use "Find and Replace" function to find multiple same cell and replace them with value I want. But I have to replace a long list of values at once. For example, I have a range of sample IDs and I want to replace them simultaneously.
I used Visual Basic for Applications (VBA) to solve this task on Excel. I would like to share this simple trick that I learned that could save you from spending all day doing repetitive work manually.
1. First, create conditions that contain the original values and the new values that you to replace with.
I used Visual Basic for Applications (VBA) to solve this task on Excel. I would like to share this simple trick that I learned that could save you from spending all day doing repetitive work manually.
1. First, create conditions that contain the original values and the new values that you to replace with.
2. Then open VBA window using shorcut keys ALT+F11.
3. Create a Module (Click Insert > Module) using the following VBA code.
Sub MultiFindNReplace()
'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub
'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub
4. Click the "Run" button (or press F5) to run the code.
5. Specify the data range to be replaced in the prompt box. In my case, A1-A10.
6. Then, select the conditions created at the beginning as replacement range.
7. Once you click "OK", all the values will be replaced immediately!