Excel - Find and Replace a Range

November 2016




Issue


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

Can anyone let me know if this is possible?

Solution


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 
Next 

End Sub

Note


Thanks to Castellano for this tip on the forum.

Related :

This document entitled « Excel - Find and Replace a Range » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.