How to Find and Replace Values Greater Than or Less Than A Fixed Value in Excel

This post will guide you on how to find and replace values greater than or less than a fixed value in Microsoft Excel using VBA code.

 In this post, we will walk you through the steps to create a VBA macro that will search for values that meet certain criteria and replace them with a new value.

1. Video: Find and Replace Values Greater Than or Less Than A Fixed Value

This video tutorial will guide you on how to use VBA to find and replace values greater than or less than a fixed value in Excel, allowing you to quickly modify your data.

2. Find and Replace Values Greater Than A Fixed Value by VBA Code

Suppose we have a list of scores, and we want to replace values equal and greater than 60 to text ‘PASS’, this work cannot be implemented by ‘Find and Replace’ function due to this function is only used for replacing a fixed value to another fixed value. Actually, we can edit VBA code to implement this special ‘Find and Replace’. Setting a fixed value in code, then after running macro, all values greater than this fixed value can be replaced with another value properly.

Just do the following steps:

Step1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

Step2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1:

Sub FindAndReplace()
    Dim UpdateRng As Range
    Dim SelectRng As Range
    Set SelectRng = Application.Selection
    Set SelectRng = Application.InputBox("Select a Range", "Find and Replace", SelectRng.Address, Type:=8)
    For Each UpdateRng In SelectRng
        If UpdateRng.Value >= 60 Then
            UpdateRng.Value = "PASS"
        End If
    Next
End Sub

Step3: Save the macro and then quit Microsoft Visual Basic for Applications.

Step4: Click Developer->Macros to run Macro.

You can also press Alt+F8 to trigger Macro directly.

Step5: Select ‘FindAndReplace’ and click Run.

Step6: FindAndReplace dialog pops up. Enter Select Range $B$2:$B$8. In this step you can select the range you want to replace values.

Step7: Click OK. Verify that values equal to or greater than 60 are replaced with ‘PASS’ properly.

3. Find and Replace Values Less Than A Fixed Value by VBA Code

If you want to find and replace values less than a fixed value, just use the below vba code:

Sub FindAndReplace()
    Dim UpdateRng As Range
    Dim SelectRng As Range
    Set SelectRng = Application.Selection
    Set SelectRng = Application.InputBox("Select a Range", "Find and Replace", SelectRng.Address, Type:=8)
    For Each UpdateRng In SelectRng
        If UpdateRng.Value < 60 Then
            UpdateRng.Value = "Fail"
        End If
    Next
End Sub

You just need to run the this code repeate the above Step4-Step6.

4. Conclusion

When dealing with large data sets, it can be tedious and time-consuming to manually search and replace values. By using VBA code, you can automate this process and quickly modify your data to meet your needs.