QP School
Find a specific value in a range and replace it with a new value - Printable Version

+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Find a specific value in a range and replace it with a new value (/showthread.php?tid=5259)



Find a specific value in a range and replace it with a new value - Qomplainerz - 07-27-2023

Example:

Sub FindAndReplace()
    Dim searchRange As Range
    Dim findValue As Variant
    Dim replaceValue As Variant

    'Set the range where you want to find and replace values
    Set searchRange = Range("A1:A10")

    'Specify the value to find and the value to replace it with
    findValue = "OldValue"
    replaceValue = "NewValue"

    'Find and replace the value in the range
    searchRange.Replace What:=findValue, Replacement:=replaceValue, LookAt:=xlWhole
End Sub

Explanation:

The code defines a subroutine named FindAndReplace.
It declares variables for the search range (searchRange), the value to find (findValue), and the value to replace it with (replaceValue).
Set searchRange = Range("A1:A10") specifies the range A1:A10 where you want to find and replace values.
findValue = "OldValue" and replaceValue = "NewValue" set the values to be found and replaced, respectively.
The searchRange.Replace method is used to find and replace the value. 
What:=findValue specifies the value to find, Replacement:=replaceValue specifies the value to replace it with, and LookAt:=xlWhole indicates that it should find whole matches.