Replace All Occurences Of Different Text

chatguy · Feb 6th 2008

Hi All,

May I ask how to write a search and replace macro, but instead of writing:

Code

Cells.Replace "OldText", "NewText"

... may I ask how to independently read this "search" text into a string (hopefully a string that can handle 'more' than 255 characters per cell, even in Excel 2000) then replacing that search text("OldText") with ("NewText") step-by-step?

(Also pls. note that "OldText" can appear multiple times in one cell)

Thanks so much in advance!!
CG

Dave Hawley · Feb 7th 2008

Re: Search And Replace Via Reading Into A String, Then Replacing Text

Code

Sub ReplaceAll()
Dim lLoop As Long
Dim strReplace As String
Dim strReplaceWith As String


    For lLoop = 1 To 5
        strReplace = Choose(lLoop, "Cat", "Blue", "Car", "Old", "Rusty")
        strReplaceWith = Choose(lLoop, "Dog", "Red", "Bike", "New", "Shiny")
        Cells.Replace strReplace, strReplaceWith
    Next lLoop
End Sub

Display More

shg · Feb 7th 2008

Re: Replace All Occurences Of Different Text

Code

Range("A1").Value = Replace(Range("A1").Value, "Old", "New")

Bill Rockenbach · Feb 7th 2008

Re: Replace All Occurences Of Different Text

or a spin off of Dave's code

Code

Sub ReplaceAll2()
    Dim lLoop As Long
    Dim strReplace
    Dim strReplaceWith
    
    strReplace = Array(lLoop, "Cat", "Blue", "Car", "Old", "Rusty")
    strReplaceWith = Array(lLoop, "Dog", "Red", "Bike", "New", "Shiny")


    For lLoop = 0 To 4
        Cells.Replace strReplace(lLoop), strReplaceWith(lLoop)
    Next lLoop
End Sub

Display More

or

Code

Sub ReplaceAll3()
    Dim lLoop As Long
    Dim strReplace
    Dim strReplaceWith
    
    'Two columns, Replace string and With String
    strReplace = Sheet2.Range("A1:A5")
    strReplaceWith = Sheet2.Range("B1:B5")


    For lLoop = 1 To 5
        Sheet1.Cells.Replace strReplace(lLoop, 1), strReplaceWith(lLoop, 1)
    Next lLoop
End Sub

Display More

[hr]*[/hr] Auto Merged Post;[dl]*[/dl]and yet another

Code

Sub ReplaceAll4()
    Dim lLoop As Long
    Dim strReplace As Range
    
    'Two columns, Replace string and With String
    For Each strReplace In Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))
        Sheet1.Cells.Replace strReplace, strReplace.Offset(0, 1)
    Next strReplace
End Sub

Dave Hawley · Feb 7th 2008

Re: Replace All Occurences Of Different Text

Bill, that's a much better way than using Choose. Great work.

Bill Rockenbach · Feb 7th 2008

Re: Replace All Occurences Of Different Text

Thanks Dave

shg · Feb 7th 2008

Re: Replace All Occurences Of Different Text

I think the issue is that rng.replace doesn't work when the text in the cell is long. I didn't do anything resembling an exhaustive test, but did confirm it's so. Ergo my post, which (in my limited test) avoids the problem.

Replace All Occurences Of Different Text

Participate now!

Share

Users Viewing This Thread