Change a specific text value every spot it appears in a workbook.

Posted by GLITZ on December 15, 2000 5:24 AM

I have a employee's listed by their initials throughout a certain workbook. I want to change their initials to their name every time they appear.

Example:

Posted by EXAMPLE on December 15, 2000 5:27 AM

I LEFT OUT MY EXAMPLE-->
M.C. appears ten times on 4 different tabs. I want to put "Micheal Carter" in every cell that has "M.C."

Thank You

Posted by Loren on December 15, 2000 7:18 AM

Use find and replace

Posted by Tim Francis-Wright on December 15, 2000 9:14 AM

There might be an easier way to do this, but this
VBA subroutine will do what you need:

' Based on one of Ivan's posts earlier this year
Sub SandR()
Dim Sh
Dim TextCells As Range
Dim OldText As String, NewText As String

On Error Resume Next

OldText = Application.InputBox("What is the text that you want to replace?")
NewText = Application.InputBox("What is the new text?")

For Each Sh In ActiveWorkbook.Sheets
Sh.Activate
Set TextCells = Sh.Range("A1").SpecialCells(xlCellTypeConstants, xlTextValues)

If Not (TextCells Is Nothing) Then
TextCells.Select
Selection.Replace What:=OldText, Replacement:=NewText, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
Next Sh
End Sub