Find and Replace cells with specific value in a range

How to find and replace the cells with specific value in a selected range using Excel and VBA methods

METHOD 1. Find and Replace cells with specific value in a range

EXCEL

Select range > Home tab > Editing group > Find & Select > Replace > Enter value in Find what: input box > Enter value to be replaced with in Replace with: input box > Select Sheet in the Within: input box > Click Replace All

1. Select the range in which you want to find and replace specific value.
Note: in this example we have selected range reference (B3:C9).

Select range from which to find and select cells with specific value

2. Select the Home tab.

3. Select Find & Select in the Editing group.
4. Click Replace.

Select Find and Select and click Replace

6. Enter the value that you want to find and replace in the Find what: input box.
7. Enter the value that you want to replace with in the Replace with: input box.
8. Select Sheet in the Within: input box.
9. Click Replace All.

Enter value to replace and replace with select Sheet and click Replace All

10. This image shows the result of replacing the specific value of 500 with 400

Result of specific value being replaced with another

METHOD 1. Find and Replace cells with specific value in a range using VBA

VBA

Sub Replace_specific_value()

'declare variables
Dim ws As Worksheet
Dim xcell As Object
Dim Rng As Range

Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B3:C9")

'check each cell in a specific range if the criteria is matching and replace it
For Each xcell In Rng

xcell = Replace(xcell, 500, 400)

Next xcell

End Sub

ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet which captures the range of cells in which you want to replace the cells with a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Specific Value to Replace: Select the value that you want to replace by changing the value of "500" in the VBA code.
Specific Value to Replace with: Select the value that you want to replace with by changing the value of "400" in the VBA code.
Range: Select the range that captures the cells in which you want to replace the cells with a specific value by changing the range reference ("B3:C9") in the VBA code.

Explanation about how to find and replace cells with specific value in a range

EXPLANATION

This tutorial shows how to find and replace cells with specific value in a range using Excel and VBA methods.

This tutorial provides one Excel method that can be applied to find and replace cells with a specific value in a selected range. This method replaces all of the values in the selected range. If a cell captures this value in addition to others it will still replace the specified value in the cell. To only replace the the value if it matches the entire cell content, then you need to select the Match entire cell content in the Find and Replace dialog window.

Using VBA you can find and replace cells with a specific value in a specified range. By applying this VBA method it will replace all occurrence of the specified value in the selected range, even if the cell contains other content.

Related Topic	Description	Related Topic and Description
Find and select cells with specific value in a worksheet	How to find and select cells with specific value in a worksheet using Excel and VBA methods
Find and select cells with specific value in a range	How to find and select the cells with specific value in a selected range using Excel and VBA methods
Find and Replace cells with specific value in a worksheet	How to find and replace the cells with specific value in an entire worksheet using Excel and VBA methods
Find and select cells between specific values in a range	How to find and select cells between specific values in a range using Excel and VBA methods
Fill blank cells with a specific value	How to fill blank cells with a specific value using Excel and VBA methods