What is the syntax to replace a value in a whole workbook?

Dim Sht As Worksheet
For Each Sht In Worksheets
   Sht.Cells.Replace What:=<old value>, Replacement:=<new value>, LookAt:=xlPart, MatchCase:=False
Next
'example - replaces all instances of person's name
'Old first/last name are in A1, B2 (respectively) in Sheet "Names"
Dim Sht As Worksheet
For Each Sht In Worksheets
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 1).Value, Replacement:=txtNameFirst.Value, LookAt:=xlPart, MatchCase:=False
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 2).Value, Replacement:=txtNameLast.Value, LookAt:=xlPart, MatchCase:=False
Next

 

 

Advertisements

What is the syntax to find and replace a value?

Sht.Cells.Replace What:=<old value>, Replacement:=<new value>, LookAt:=xlPart, MatchCase:=False
'example - replaces all instances of person's name
'Old first/last name are in A1, B2 (respectively) in Sheet "Names"
Dim Sht As Worksheet
For Each Sht In Worksheets
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 1).Value, Replacement:=txtNameFirst.Value, LookAt:=xlPart, MatchCase:=False
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 2).Value, Replacement:=txtNameLast.Value, LookAt:=xlPart, MatchCase:=False
Next

 

 

What is the syntax to replace a string with another?

Replace(String, find, replacewith, start, count, compare)
'string - string that will be searched; mandatory parameter
'find - string part that will be replaced; mandatory parameter
'replacewith - string part that will be inserted; mandatory parameter
'start – integer specifying the starting position of search; optional parameter where default is 1
'count - integer specifying number of times the replacement has to be performed; optional parameter
'compare - integer specifying the comparison method: 0 for binary, 1 for textual; optional parameter
'examples
Dim sentence As String
sentence = "The red fox"
MsgBox (Replace(sentence, "red", "blue", 1, 1)) 'The blue fox
MsgBox (Replace(sentence, "fox", "panda", 1)) 'The red panda
MsgBox (Replace(sentence, "fox", "panda", 9)) 'panda
MsgBox (Replace(sentence, "The", "My")) 'My red fox