A few words of thanks would be greatly appreciated.

Excel - Find and Replace a Range


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.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.


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).

1 Comment