Excel - Find and Replace a Range

March 2017




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


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