How to find string and replace it?

[iand5]

I already have the code for replacing a string with a different string. It works when I know what the contents of the cell are. For example, in this case "A1" contains the string "test".

Sub Button1_Click()

Dim OriginalText As String
Dim CorrectedText As String


OriginalText = Range("A1").Value
CorrectedText = Replace(OriginalText, "test", "exam")


Range("A1").Value = CorrectedText


End Sub

I am trying to figure out how to replace a string when I don't know the contents of the cell.

 

[Michael M]

If you don't know the contents of the cell, how do you know if it needs replacing ???

 

[iand5]

In the case I don't know the cell number where the string I'm trying to replace is located.

 

[My Aswer Is This]

So you want to search the entire sheet or entire workbook for What and replace it with what.

For example look for Duck and replace it with Cat.
We need information like this.

 

[Logit]

.
I'm with Michael M .. if you don't know the cell address and you don't know the term you are seeking to replace,
how else is there to pinpoint what you are replacing ?

 

[Michael M]

Assuming say range A1 to A10....something like this will do the trick, but I'm guessing there will be a much shorter version available

Sub Button1_Click()
Dim r As Long
For r = 1 To 10
    If InStr(Range("A" & r).Value, "test") Then
        Range("A" & r).Value = Replace(Range("A" & r).Value, "test", "Exam")
    End If
Next r
End Sub

 

[iand5]

So you want to search the entire sheet or entire workbook for What and replace it with what.

For example look for Duck and replace it with Cat.
We need information like this.

Sorry, it's been a long day heh.

Yes, that's pretty much that's what I am trying to do.

Search the entire sheet. Look for a string "Duck"(for example) and replace it with "Cat".

 

[My Aswer Is This]

So would you be looking for:

The Duck is Yellow.
And replace Duck with

The flower is Yellow

Or are you looking for just Duck in a cell and replace Duck with Flower.

Have you looked into the built in function in Excel.
On the ribbon you should see Find and inside Find you have a replace section.

 

[Logit]

.

Option Explicit


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
     
    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)
      
    Set WS = ThisWorkbook.ActiveSheet
   
        WS.Cells.Replace What:=Search, Replacement:=Replacement, _
        LookAt:=xlPart, MatchCase:=False
    
     
End Sub

 

[iand5]

.

Option Explicit


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
     
    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)
      
    Set WS = ThisWorkbook.ActiveSheet
   
        WS.Cells.Replace What:=Search, Replacement:=Replacement, _
        LookAt:=xlPart, MatchCase:=False
    
     
End Sub

Thanks!

What if I already know what string I want to replace and its replacement string?
Just search the entire worksheet without having to prompt?