VBA to replace one word in a cell with another (Noob)

[Tooom]

Hi this is my first post - I don't think it will be too difficult for you guys but I have only recently started to learn VBA!

I found the below code from searching the web and have tried adapting it without success as I could not find any code to fit my exact purpose.

Sub Colorize_Word() 
Dim Found As Range, FirstFound As String 
Dim FindWhat As String, MyColor As Long, Counter As Integer 

MyColor = 3 

FindWhat = LCase(InputBox("Find what word?", "Color word")) 
If FindWhat = "" Then Exit Sub 

Set Found = Cells.Find(What:=FindWhat, _ 
LookIn:=xlFormulas, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, _ 
SearchDirection:=xlNext, MatchCase:=False, _ 
SearchFormat:=False) 

If Not Found Is Nothing Then 
FirstFound = Found.Address 
Application.ScreenUpdating = False 
Do 
Set Found = Cells.FindNext(Found) 
Counter = InStr(LCase(Found), FindWhat) 
Do 
Found.Characters(Counter, Len(FindWhat)).Font.ColorIndex = MyColor 
Counter = InStr(Counter + 1, LCase(Found), FindWhat) 
Loop While Counter > 0 
Loop Until Found.Address = FirstFound 
Application.ScreenUpdating = True 
Else 
MsgBox "No match found." 
End If 

End Sub

This code finds any string in a cell of your choice and colors it red. I would really like to replace the string with my own string of text. I couldn't use the simple find and replace function as this replaces the WHOLE cell content not just one word.

I believe the line of code that needs editing most is this one

Do
Found.Characters(Counter, Len(FindWhat)).Font.ColorIndex = MyColor
Counter = InStr(Counter + 1, LCase(Found), FindWhat)

But I cannot figure out how to use the replace function with this. Very grateful for any enlightenment!

 

[BrianMH]

This code actually finds a range and the works with that range. If you already know the "cell of your choice" you can just use the replace function.

range("A1").Value = replace(range("A1").Value,"ReplaceMe","WithMe")

BTW find and replace can just replace the word that partially exists. You just need to lookat xlpart instead of xlwhole.

 

[NewOrderFac33]

How about:

Sub ReplaceSubString()


    Dim FindWhat As String
    Dim ReplaceWhat As String


    FindWhat = LCase(InputBox("Replace what string?", "Source string"))
    If FindWhat = "" Then Exit Sub
    
    ReplaceWhat = LCase(InputBox("Replace with what?", "Replacement string"))
    If ReplaceWhat = "" Then Exit Sub


    Cells.Replace What:=FindWhat, Replacement:=ReplaceWhat, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
End Sub

You can replace "cells.replace" with "selection.replace" or a range address to change where you're searching and replacing.

Cheers

Pete

 

[Tooom]

Hi Brian thanks for that, I am trying to replace one word anytime it appears in my spreadsheet which is why I thought I might need to work with a range. If I put my range of cells in your formula should that work? I will give it a go.

The Xlpart/XlWhole thing are we talking about the same find and replace? I am talking about the built in Excel version screenshotted below. (You can see from this I would like to replace anytime I have 202 in the sheet with 203). Thought I would try automate this as I will be doing it many times

 

[Tooom]

Pete that worked amazingly, Reading through the syntax help files to make sure I understand how. Thanks for teaching a man to fish =)

 

[BrianMH]

You could also make it a sub that you pass variables to if you are going to be using it multiple times.

Sub Caller()
ReplaceSubString "x", "y", Range("A1:A5")
ReplaceSubString "a", "b", Range("T1:Z15")
End Sub


Sub ReplaceSubString(FindWhat As String, ReplaceWhat As String, SearchRange As Range)
    SearchRange.Replace What:=FindWhat, Replacement:=ReplaceWhat, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
End Sub

 

[Tooom]

Brian that's interesting will try that too. Big thankyou for taking the time to look over this.

 

[NewOrderFac33]

Tooom,

Glad to be of assistance. Plus, your original post provided something to add to my own library of useful stuff.

Everyone wins!

Pete