Excel - Find and Replace a Range

October 2017


I want to find and replace a range of values on excel?

Can anyone let me know if this is possible?


I have this in my project but i can replace a range of cell.

Sub ChgInfo() 

Dim WS As Worksheet 
Dim Search As String 
Dim Replacement As String 
Dim Prompt As String 
Dim Title As String 
Dim MatchCase As Boolean 
MsgBox "This option affect all the worksheet. Is created to change ADM. PERSONNEL, PI/PD NAME or PROJECT TITLE only. " & vbCrLf & "For more info see: SOP Matrix Informes de Proyectos", vbExclamation + vbOKOnly, "Read Before" 

Prompt = "What is the original value you want to replace?" 
Title = "Search Value Input" 
Search = InputBox(Prompt, Title) 

Prompt = "What is the replacement value?" 
Title = "Search Value Input" 
Replacement = InputBox(Prompt, Title) 

For Each WS In Worksheets 
WS.Cells.Replace What:=Search, Replacement:=Replacement, _ 
LookAt:=xlPart, MatchCase:=False 

End Sub


Thanks to Castellano for this tip on the forum.
Published by aakai1056. Latest update on February 18, 2010 at 05:04 AM by aakai1056.
This document, titled "Excel - Find and Replace a Range," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).