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

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

EXCEL

Select worksheet > 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 worksheet in which you want to find and replace specific value.
Note: in this example we only want to search and select in Sheet1.

Select worksheet in which to Find and Replace

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 in an entire worksheet

Result of specific value being replaced with another

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

VBA

Sub Replace_specific_value()

'declare variables
Dim ws As Worksheet
Dim xcell As Object

Set ws = Worksheets("Analysis")

'check each cell in a specific worksheet if the criteria is matching and replace it
For Each xcell In ws.UsedRange.Cells

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.

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

EXPLANATION

This tutorial shows how to find and replace cells with specific value in a worksheet 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 an entire worksheet. This method replaces all of the values in an entire worksheet at once. 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 entire worksheet. By applying this VBA method it will replace all occurrence of the specified value in an entire worksheet, even if the cell contains other content.

Related Topic	Description	Related Topic and Description
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
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 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